How to debug stored procedures with print statements?

Question

I am trying to debug stored procedures in SQL Server Management Studio 2008. I want to insert some print statements to test some IF-statements that I know are wrong.

In order to do debugging, I tried using the PRINT '5' command.

I tried using the RAISERROR like 'RAISERROR (N'Start',10,1) WITH NOWAIT'.

But these did not show any printouts, only the result set. The message just says 1 row affected.

I attempted to write the code like so (perhaps this is an incorrect approach):

SET NOCOUNT ON         
RAISERROR (N'Start',10,1) WITH NOWAIT    
DECLARE @DocHandle INT        
DECLARE @PageSize INT, @PageIndex INT, @TOTL_CONT NUMERIC(5,0), @Paging BIT        
DECLARE @Type INT, @Search varchar(20) , @ORDE nVARCHAR(50), @SORT_ID nVARCHAR(50) 
DECLARE @CreatedOn varchar(25), @SystemGenerate bit   

What is the best way to use print statements to debug a stored procedure?

Answer 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.

Answer 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);

Answer 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.

Answer 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.

Answer 5

Look at this Howto in the MSDN Documentation: Run the Transact-SQL Debugger - it's not with PRINT statements, but maybe it helps you anyway to debug your code.

This YouTube video: SQL Server 2008 T-SQL Debugger shows the use of the Debugger.

=> Stored procedures are written in Transact-SQL. This allows you to debug all Transact-SQL code and so it's like debugging in Visual Studio with defining breakpoints and watching the variables.

• see: debugging T-SQL code: call
• see: debugging T-SQL code: watch variables