Validate SQL

[2ni]

I build a store procedure to make a search in my customers table. But i would like to validate the sql query i build berfore running it. Is there a way to do that ?

I found something with SET PARSEONLY ON....

 

[bborissov]

Just copy all your code in new query window and start it. Of course if you pass a parameters to that SP you must define the before that. Something like this:

a SP code:

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] mysp_Test (@par1 [COLOR=blue]int[/color], @par2 [COLOR=blue]varchar[/color](200))
[COLOR=blue]AS[/color]
 [COLOR=blue]BEGIN[/color]
     [COLOR=blue]SELECT[/color] *
            [COLOR=blue]FROM[/color] MyTable
            [COLOR=blue]WHERE[/color] SomeField = @par1 AND
                  OtherFielld LIKE [COLOR=red]'%'[/color]+@par2
 [COLOR=blue]END[/color]

So to test it just copy that part beteen initial BEGIN and last END and paste it in new query window:

[COLOR=blue]SELECT[/color] *
       [COLOR=blue]FROM[/color] MyTable
       [COLOR=blue]WHERE[/color] SomeField = @par1 AND
             OtherFielld LIKE [COLOR=red]'%'[/color]+@par2

and to make it work declare and set two variables as in SP:

[COLOR=blue]DECLARE[/color] @par1 [COLOR=blue]int[/color]
[COLOR=blue]DECLARE[/color] @par2 [COLOR=blue]varchar[/color](200)

[COLOR=blue]SET[/color] @par1 = 12
[COLOR=blue]SET[/color] @par2 = [COLOR=red]'bla bla bla'[/color]



[COLOR=blue]SELECT[/color] *
       [COLOR=blue]FROM[/color] MyTable
       [COLOR=blue]WHERE[/color] SomeField = @par1 AND
             OtherFielld LIKE [COLOR=red]'%'[/color]+@par2

Other way. Create your SP, the in QA or SSMS execute it with different kinds of parameters and watch the resultsets.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mrdenny]

I beleive that he's looking for a way to parse the code within his stored procedure.

The only way that I can think so doing it would be via Dynamic SQL. Unfornitually Dynamic SQL is never recommended because it usually brings in more problems than it solves.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com