fstop (5/6/2014)
Thank you all for your suggestions. Unfortunately, ganging all the create statements into one stored proc isn't working (although for some reason it create 3 out of 10). I'm getting a ton of errors such as "Incorrect syntax near the keyword 'PROCEDURE'" that indicate to me that it doesn't like have multiple CREATE statements in one proc. Also, if multiple procs have the same variable name, it doesn't like that either (and this exercise isn't worth renaming variables in all my procs).I was hoping to get suggestions on an alternative, such as creating a script that runs each separate sql file (each with one CREATE statement). Also, I believe my IT Dept has turned off my ability to run .cmd files due to security issues.
So I'm open to other ideas if you have them. Thanks
That is correct. You can have only one create procedure in a batch. Assuming you are running this in SSMS you can put a batch separator in your code. The default is "GO".
Create procedure MyProc as
..some statements
GO
Create procedure MySecondProc as
..some statements
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/