Post reply

Seeing the source of dynamic stored procedure

  • November 1, 2005 at 5:04 am

    #91602

    Hi Everyone

    I have a beefy stored procudure which takes in 6 parameters and then does a bit of logic on the where clause.  However, there is a problem with the results that are returned.

    Is there anyway I can see the final SQL code that is generated from this logic, so I can see what is going wrong.

    Cheers

    pie

  • November 1, 2005 at 5:14 am

    #601511

    Why don't you just PRINT it?

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • November 1, 2005 at 5:30 am

    #601512

    that sounds cool, but how do I go about doing that?

     

  • November 1, 2005 at 5:54 am

    #601518

    If your SP has code like

    declare @sql varchar(8000)

    set @sql = 'select * from myTable where myCol = 1'

    exec(@sql)

    You can also add in a line saying

    print @sql

    And your SQL will be printed

    Hope that helps

  • November 1, 2005 at 6:06 am

    #601522

    Another trick that I use is to create a table to house SQL statements VARCHAR(8000).  I string my SQL statement together and then write it to the table.  I ONLY do this troubleshooting.  This can be enabled/disabled with a flag parameter to perform this type of troubleshooting IF @WriteSQL = True BEGIN....

    This has helped debug several different issues.  Please note that it will take experimentation to get the statement to work (if there are ' )....

    I use this approach so I can troubleshoot "off-line" and for monitoring purposes...



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

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

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