Simple method to make debugging stored procedures easier.

The scenario: 

The client has come back with a problem, the web site is not returning the right data.  You have the latest release code for the stored procedure, but you cannot update the procedure in production, and getting the database backed up into a dev environment would take longer than the needed fix.  You know you can do selects against database, so you crack open the code for the stored procedure and you begin editing away.  

You figure out the problem, send a release to production... but you forgot to test the proc in dev, and what would be the point right... you didn't have the data to test the race condition their anyway right?  The proc fails to compile, you left your testing variables in the proc!  You now have to go back, remove your testing code, and re-submit, taking precious time and you look bad.

This is actually a pretty common scenario in software development, especially in database development when their is significant response speed required, and a high demand client/business user breathing down your neck to get something done.  So how do you mitigate this problem.  In this case the developer codes an actual fix, he is able to select from the database, get the right results, but forgot to go through the grinding steps of properly cleaning up his work for a production release.  

I have found that the best method for this kind of thing is what I call a test harness (if anyone corrects my term I will update this article).  I am not sure if this is the proper term, but it is one I have seen used.  Because of the way that MS SQL server compiles its code, you can often add data to a stored proc that will allow you to run the code of a stored procedure in place by selecting a subset of code, without having to worry that the stored procedure will fail when the entire script is run by someone trying to deploy the code.

Lets take a following simple example:

CREATE PROCEDURE dbo.MyExampleFailProc (@Input1 VARCHAR(255), 
                                        @Input2 VARCHAR(255)) 
AS 
    SELECT @Input2 AS Input1, 
           @Input1 AS Input2 

go 

EXEC dbo.MyExampleFailProc
  1, 
  2 

The client comes back and says that 1 and 2 are reversed, and we are going to pretend that this is a MUCH larger proc that has a lot of debugging required.

In the end the developer has this code sitting in his Management Studio instance

ALTER PROCEDURE dbo.MyExampleFailProc (@Input1 VARCHAR(255), 
                                        @Input2 VARCHAR(255)) 
AS 
    DECLARE @Input1 VARCHAR(255), 
            @Input2 VARCHAR(255) 

    SET @Input1 = 1 
    SET @Input2 = 2 

    SELECT @Input1 AS Input1, 
           @Input2 AS Input2

As you can see the underlying logic of the select statement is correct, but he then sends this code off to be deployed (again, pretend it is a bigger problem).

The developer gets back the following error from the 

Msg 134, Level 15, State 1, Procedure MyExampleFailProc, Line 12

The variable name '@Input1' has already been declared. Variable names must be unique within a query batch or stored procedure.

The developer then goes back and released the correct code.

ALTER PROCEDURE dbo.MyExampleFailProc(@Input1 VARCHAR(255), 
                                        @Input2 VARCHAR(255)) 
AS 
    SELECT @Input1 AS Input1, 
           @Input2 AS Input2 

So what is an easy way to avoid this kind of issue?

This is by using a test harness.  When creating your stored procedure, make an assumption that the procedure will likely be wrong at some point, and need a quick turn around, then code to mitigate this contingency.  Instead of pretending that every coding environment is pristine, everyone does the right thing the first time, and nothing bad will ever happen, always assume that something bad will happen, and it will need to be fixed.  The test harness method allows you to do this.  Basically it assumes that at some point you will not have access to change a stored proc, that you WILL have access to execute and select, and that the data you have available in dev will not match that in production and will be hard to get to.  

What can be done is to use a small bit of knowledge about how SQL Server 2008 (and thus management studio) parses comment code, and you can debug stored procedure contents much faster.

To do this, you add the following to the stored procedure, just after the "AS" statement.

/*

--Put your declarations and set operations here:

--*/

By doing this, you can select your declaration and set operations for your testing, and your main code will still deploy.  Here is an example using the defined situation above:

ALTER procedure dbo.MyExampleFailProc

(

      @Input1 varchar(255)

      ,@Input2 varchar(255)

     

)

AS

/*

--Put your declarations and set operations here:

DECLARE @Input1 varchar(255)

      ,@Input2 varchar(255)

SET  @Input1 = 1

SET  @Input2 = 2

--*/

SELECT

       @Input1 as Input1

      ,@Input2 as Input2

Now if the developer selects the code from the DECLARE statement down, it will execute:

DECLARE @Input1 varchar(255)

      ,@Input2 varchar(255)

SET  @Input1 = 1

SET  @Input2 = 2

--*/

SELECT

       @Input1 as Input1

      ,@Input2 as Input2

And return valid test results:

Input1 Input2

1       2

(1 row(s) affected)

This works because when you select this in this manner, it does not see */ as a trailing comment block, it just comments it out like anything else, but when the code is executed in total, it sees it as a trailing comment block, and ignores the declare/set statements that were being used for debugging.

I have used this many times, and I have found it to be helpful to me for testing.  It violates good formatting principles that some have, but I think that the time savings when speed sometimes trumps careful coding, can be a real help.