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

User · Accepted Answer

If you re using MSSQL Server management studio print statements will print out under the messages tab not under the Results tab     Print statements will appear there

User · Answer

try using   RAISERROR  your message here     0 1  WITH NOWAIT   you could also try switching to  Results to Text  it is just a few icons to the right of  Execute  on the default tool bar   With both of the above in place  and you still you 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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to debug stored procedures with print statements?

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures