How to debug stored procedures with print statements?

Solution 1:

If you're using Microsoft SQL Server Management Studio (SSMS), print statements will print out under the Messages tab, not under the Results tab.

Print statements will appear there.

Solution 2:

Here is an example of print statement use. They should appear under the messages tab as a previous person indicated.

Declare @TestVar int = 5;

print 'this is a test message';
print @TestVar;
print 'test-' + Convert(varchar(50), @TestVar);

Solution 3:

Before I get to my reiterated answer; I am confessing that the only answer I would accept here is this one by KM. above. I down voted the other answers because none of them actually answered the question asked or they were not adequate. PRINT output does indeed show up in the Message window, but that is not what was asked at all.

Why doesn't the PRINT statement output show during my Stored Procedure execution?
The short version of this answer is that you are sending your sproc's execution over to the SQL server and it isn't going to respond until it is finished with the whole transaction. Here is a better answer located at this external link.

• For even more opinions/observations focus your attention on this SO post here.
• Specifically look at this answer of the same post by Phil_factor (Ha ha! Love the SQL humor)
• Regarding the suggestion of using RAISERROR WITH NOWAIT look at this answer of the same post by JimCarden

Don't do these things

1. Some people are under the impression that they can just use a GO statement after their PRINT statement, but you CANNOT use the GO statement INSIDE of a sproc. So that solution is out.
2. I don't recommend SELECT-ing your print statements because it is just going to muddy your result set with nonsense and if your sproc is supposed to be consumed by a program later, then you will have to know which result sets to skip when looping through the results from your data reader. This is just a bad idea, so don't do it.
3. Another problem with SELECT-ING your print statements is that they don't always show up immediately. I have had different experiences with this for different executions, so don't expect any kind of consistency with this methodology.

Alternative to PRINT inside of a Stored Procedure
Really this is kind of an icky work around in my opinion because the syntax is confusing in the context that it is being used in, but who knows maybe it will be updated in the future by Microsoft. I just don't like the idea of raising an error for the sole purpose of printing out debug info...

It seems like the only way around this issue is to use, as has been explained numerous times already RAISERROR WITH NOWAIT. I am providing an example and pointing out a small problem with this approach:

ALTER
--CREATE 
    PROCEDURE [dbo].[PrintVsRaiseErrorSprocExample]
AS
BEGIN
    SET NOCOUNT ON;

    -- This will print immediately
    RAISERROR ('RE Start', 0, 1) WITH NOWAIT
    SELECT 1;

    -- Five second delay to simulate lengthy execution
    WAITFOR DELAY '00:00:05'

    -- This will print after the five second delay
    RAISERROR ('RE End', 0, 1) WITH NOWAIT
    SELECT 2;
END

GO

EXEC [dbo].[PrintVsRaiseErrorSprocExample]

Both SELECT statement results will only show after the execution is finished and the print statements will show in the order shown above.

Potential problem with this approach
Let's say you have both your PRINT statement and RAISERROR statement one after the other, then they both print. I'm sure this has something to do with buffering, but just be aware that this can happen.

ALTER
--CREATE 
    PROCEDURE [dbo].[PrintVsRaiseErrorSprocExample2]
AS
BEGIN
    SET NOCOUNT ON;

    -- Both the PRINT and RAISERROR statements will show
    PRINT 'P Start';
    RAISERROR ('RE Start', 0, 1) WITH NOWAIT
    SELECT 1;

    WAITFOR DELAY '00:00:05'

    -- Both the PRINT and RAISERROR statements will show
    PRINT 'P End'
    RAISERROR ('RE End', 0, 1) WITH NOWAIT
    SELECT 2;
END

GO

EXEC [dbo].[PrintVsRaiseErrorSprocExample2]

Therefore the work around here is, don't use both PRINT and RAISERROR, just choose one over the other. If you want your output to show during the execution of a sproc then use RAISERROR WITH NOWAIT.

Solution 4:

Try using:

RAISERROR('your message here!!!',0,1) WITH NOWAIT

You could also try switching to "Results to Text", which is just a few icons to the right of "Execute" on the default tool bar.

With both of the above in place, and if you still do not see the messages, make sure you are running the same server/database/owner version of the procedure that you are editing. Make sure you are hitting the RAISERROR command, make it the first command inside the procedure.

If all else fails, you could create a table:

create table temp_log (RowID int identity(1,1) primary key not null
                      , MessageValue varchar(255))

then:

INSERT INTO temp_log VALUES ('Your message here')

then after running the procedure (provided no rollbacks), just select the table.