How Can i display the output of SQL "PRINT" Command in C#?
Solution 1
You need to subscribe to the SqlConnection.InfoMessage
Event.
MSDN has some example code here.
Solution 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 '+@test+' 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.
Fady Kamal
Updated on June 19, 2022Comments
-
Fady Kamal almost 2 years
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 '+@test+' 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 '+@test+' 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 '+@test+' 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 '+@test+' 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 '+@test+' is:'+' '+CAST (@count5 AS VARCHAR(MAX))) END
-
Martin Smith over 12 yearsHmm, Hadn't looked at why they wanted to do this. The "proper" way of doing it IMO would be to return a simple result set with 2 columns
test,count
-
Martin Smith over 12 years@BrijeshMishra - No
SqlConnection
is specific for SQL Server. Not sure if other RDBMSs (other than Sybase) have equivalents toPRINT
anyway... -
competent_tech over 12 years@MartinSmith: That approach could be problematic if their query doesn't yield any results or if they change it in the future to include multiple results. I have found it to be much more reliable and future-proof to include status or error messages like this "out of band" in parameters.
-
Martin Smith over 12 yearsThese aren't status messages. This is the only data returned by the stored procedure.
-
Fady Kamal over 12 yearsyes you are right the problem is i am not the one who wrote the procedures so i just focuses on connecting it with asp.net Thank you both answers were helpful for me
-
competent_tech over 12 years@MartinSmith: Wow, I just took a second look at the SP and realized what is actually happening. I've never seen a structure quite like that and realize now what is happening.
-
competent_tech over 12 years@MartinSmith: Now that I understand, I have updated the answer with a suggested rewrite of the SP and a clarification of my earlier answer. Thanks for the sharp eyes.