I have 50-60 stored procedures in my application and these are stored in different files as per file one stored procedudre.
I want to deploy my applicaiton. I want to create a batch or Single SQL file to create all stored precodures in on go.
My objective is to ensure that No stored procedure has left while deploying the applicaiton.
One option is to put all the script to a single .sql file and run it on the server. You can do this by running the DOS command as follows:Code Snippet
COPY *.sql AllProcedures.sql
After you run this command (assuming that all the .SQL files are in a single folder) you will get a new file named "allprocedures.sql" which contains the definition of all the stored procedures (you had in the folder).
You can run this code on your server. probably you can use osql.exe and pass the filename as the argument or run it from management studio. This article might help understand osql.exe: http://www.databasejournal.com/features/mssql/article.php/3403331
Regardless of how you may consolidate all the different files into one single file with all procs, it's important that the original files ends with a GO, or that you by some other means add a GO between each 'small file'.
So that you end up something like this;
and so on.
The reason being that a 'create procedure' must be the first statement in a batch, else you'll get errors when you try to run the whole lot on top of eachother.