Post reply

testing stored procedure

  • September 2, 2010 at 6:47 am

    #141949

    I would like to know how to debug stored procedures from sql server management studio 2005. I basically would like to know how to 'step though' or debug a stoed procedure.

  • September 2, 2010 at 7:13 am

    #1215576

    What I generally do is to add a parameter to the procedure:

    @debug tinyint = 0

    Then, within the code, do things like:

    if @debug > 0 select ....

    if @debug > 1 select ....

    This allows me to control when to show what information, and based on the value I passed in it can provide more and more detailed information.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 2, 2010 at 8:20 am

    #1215640

    Thank you very much for your answer!

    However, is there a way to see all and/or most of the values that are being used? My problem is I just inherited lots of stored procedures that use lots of temp table. These temp tables are used alot like a programmer wrtiting code.

  • September 2, 2010 at 8:30 am

    #1215647

    To see the value of any variable, just select @variable.

    To see the contents of temp tables, just select * from #temptable.

    Of course, this will need to be done within the stored procedure, so use the @debug parameter as demonstrated earlier.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply