Check if stored procedures have syntax errors

Question

I have many stored procedures in my database. We are constantly changing the data structure (we are in development) Is there a tool that will tell me which stored procedures will not compile?

When you create a stored procedure, this prevents the presence of an invalid table or column, but if you change the name of the column after creating the stored procedure, your proc is invalid. I would like to get a list of those invalid processes.

Hooray!

Joseph

Answer 1

This tool can be used with CodeProject, which uses built-in SQL functions and parameters to generate a text file with invalid stored procedures.

http://www.codeproject.com/KB/database/validatingsql.aspx

Answer 2

It is best to write a few tests of the database modules, if you are using VS2008, then it is quite simple to do, but it is time consuming.

But then you can run your tests and make sure everything works as you expect, or get a list of features that you need to look at.

You can find this article on testing database modules to be interesting, but I believe this may require the VS Team System .:

http://www.developer.com/db/article.php/3758601/Introducing-Visual-Studio-Team-System-2008-Database-Unit-Testing.htm

Answer 3

Create procedure data_tranfer (@C1 int) AS BEGIN INSERT INTO coindesk (UPDATED, UPDATEDISO, UPDATEDUK, USDCODE, USDSYMBOL, USDRATE, USDDESCRIPTION, USDRATE_FLOAT, GBPCODE, GBPSYMBOL, GBPRATE, GBPDESCRIPTION, GBPRATE_FLOAT, EURCODE, EURSYMBOL, EURRATE, EURDESCRIPTION, EURRATE_FLOAT) SELECT UPDATED, UPDATEDISO, UPDATEDUK, USDCODE, USDSYMBOL, USDRATE, USDDESCRIPTION, USDRATE_FLOAT, GBPCODE, GBPSYMBOL, GBPRATE, GBPDESCRIPTION, GBPRATE_FLOAT, EURCODE, EURSYMBOL, EURRATE, EURDESCRIPTION, EURRATE_FLOAT FROM stagcoindesk WHERE C1 = @C1 END;