Testing if Stored Procs "Parse"/"Compile" from code

Question

Hi guys

I am looking at doing some automated unit testing and I was wondering if you know of any way that one can "Parse"/"Compile" a stored proc(s) from code (i.e. from within .net).

The case I am trying to catch is if someone makes a change to a table (i.e. removes a column) and that breaks a stored proc somewhere that they don't know about. Hence I think I'm wanting to do the equivalent of what "parse" does within SQL Server Management Studio. I don't want to execute the stored proc, just check it.

Cheers Anthony

EDIT: I agree that testing if it compiles isn't a good test, but calling each stored proc I have (1000+) is a very large undertaking considering that I would have to setup an unimaginable amount of test data, conditions and code, so that I can call the stored proc without it failing due to data issues or me not supplying the SP with the correct parameters. But I might be able to get a quick win by testing if it compiles...

Answer 1

I'm not sure how you'd test if it is a valid stored proc or not, but just testing whether it compiles or not is not a good test, you'd need to call it and check the return to make sure it returns the correct data.

have a look at this question http://stackoverflow.com/questions/1177659/sql-server-way-to-syntax-check-all-stored-procedures

and also here http://stackoverflow.com/questions/1060512/recompile-stored-procs

Answer 2

What SSMS 'parse' does is running the batch with SET PARSEONLY ON. You can also try SET FMTONLY ON