i am having a SQL Procedure that always returns “PRINT” Command and i want to extract the output of this “PRINT” Command i.e the procedure in C# how can i do that ? here is the PROCEDURE
ALTER PROC ResultsPoll
@pollid INT
AS
DECLARE @count1 INT
DECLARE @count2 INT
DECLARE @count3 INT
DECLARE @count4 INT
DECLARE @count5 INT
DECLARE @test VARCHAR(MAX)
DECLARE @value VARCHAR(MAX)
SELECT @count1 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a1
SELECT @count2 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a2
SELECT @count3 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a3
SELECT @count4 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a4
SELECT @count5 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a5
SELECT @test=Polls.a1 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5e751e2a3b2d2a">[email protected]</a>+' is:'+' '+CAST (@count1 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a2 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ffd4bf8b9a8c8b">[email protected]</a>+' is:'+' '+CAST (@count2 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a3 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e1caa195849295">[email protected]</a>+' is:'+' '+CAST (@count3 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a4 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b992f9cddccacd">[email protected]</a>+' is:'+' '+CAST (@count4 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a5 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="95bed5e1f0e6e1">[email protected]</a>+' is:'+' '+CAST (@count5 AS VARCHAR(MAX)))
END
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
You need to subscribe to the SqlConnection.InfoMessage Event.
MSDN has some example code here.
Method 2
The proper approach to this is to record the value in an output parameter, then in the stored procedure, print the output parameter value.
For example:
ALTER PROC ResultsPoll @pollid INT , @message varchar(max) OUTPUT AS SET @message = '' ... IF(@test IS NOT NULL) BEGIN SET @message = 'Number of students who chose '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c5ee85b1a0b6b1">[email protected]</a>+' is:'+' '+CAST (@count1 AS VARCHAR(MAX)) END ... PRINT(@message)
Then, in your code, retrieve the value of the output parameter.
Update
The above suggestion will only work if there is a single status or error message that is being returned. On closer review of the stored procedure, I realized that this is not the case with this stored procedure since the print statements are used to return data to the calling application.
Now that I understand this, I suggest that, if possible, the stored procedure be rewritten as follows:
ALTER PROC ResultsPoll @pollid INT AS SELECT result = 'Number of students who chose ' + MAX(Polls.a1) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a1 UNION SELECT result = 'Number of students who chose ' + MAX(Polls.a2) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a2 UNION SELECT result = 'Number of students who chose ' + MAX(Polls.a3) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a3 UNION SELECT result = 'Number of students who chose ' + MAX(Polls.a4) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a4 UNION SELECT result = 'Number of students who chose ' + MAX(Polls.a5) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a5
With this, you can just process the returned rows, which will have a single column called result.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0