How to Script Creation of Multiple Stored Procedures

Question

How to Script Creation of Multiple Stored Procedures

fstop

Default port

Points: 1482
More actions
May 5, 2014 at 4:37 pm

#286466

I have about 50 Stored Procs and UDFs that need to be created in a test database each time the database is re-built. The Stored Proc creation scripts are stored in individual .sql files.
What's the most efficient way to script this process rather than running each creation script individually.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

You can generate the script for the required Stored procedures. Now you do have script ready for all required procedures. Create ALLPROC procedure which include all this code to create all required procedures. Now you can reate a job to execute this ALLPROC procedure and when ever reqired you can execute job.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

rf44 SSCommitted Points: 1515 More actions · Answer 2

Here's what I use:

1. In the folder where the SQL scripts as stored, create a file named mergescript.cmd.

2. Open this file and paste this into it:

@IF EXIST scriptdb.sql DEL scriptdb.sql

FOR %%C IN (*.sql) DO TYPE %%C >> scriptdb.txt

@REN scriptdb.txt scriptdb.sql

3. When you execute mergescript.cmd, a file named scriptdb.sql is created (or replaced if one already exists). This file contains all the individual script files merged together.

4. You can than load scriptdb.sql in SSMS or have it executed from the command line (osql, sqlcmd, etc.).

Warning: If the different objects must be created in a defined order because of their dependencies, you'll perhaps need to prefix each individual source files with a numeric value (e.g. 01-Fn_Get_List_ID.sql, 02-Proc_Tbl_Reclamations_Update.sql because Proc_Tbl_Reclamations_Update uses Fn_Get_List_ID).

Have a nice day!

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 3

Check out Visual Studio 2012/Visual Studio 2013, with SQL Server Data Tools. You can create a database project and reverse engineer a database and then execute whichever scripts you want/build a deployment against a database. Hope that this helps.

Thanks...Chris

SQL learner22 Default port Points: 1487 More actions · Answer 4

I WOULD RIGHT CLICK DATABASE AND CLICK TASKS

SELECT GENERATE SCRIPS

AND SELECT, SELECT SPECIFIED DATABASE OBJECTS OPTION AND SELECT OBJECTS TO SCRIPT

AND SELECT WHERE TO SAVE AND YOU SCRIPT OUT ALL THE OBJECTS AS CREATE

fstop Default port Points: 1482 More actions · Answer 5

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

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/

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 7

Create a batch file (.bat) that uses the SQLCMD command with the list all of the scripts required. The file contents would be similar to below.

SQLCMD -S "servername" -d "databasename" -E -i "<PATH>\sqlfile1.sql" -o "c:\temp\sqlfile1.txt"

SQLCMD -S "servername" -d "databasename" -E -i "<PATH>\sqlfile2.sql" -o "c:\temp\sqlfile2.txt"

Of course, the order of the scripts is important!

You could create the .bat file by iterating through the files in a directory and plugging the file name into the appropriate places and saving the file to disk.

Thanks...Chris

rf44 SSCommitted Points: 1515 More actions · Answer 8

The solution I provided does work, I've been using it for several years. However, you need to take certain precautions in the way the procedures are written:

1. Always begin each script with:

USE [DATABASE]

GO

2.Always follow with a statement to drop the prodecure if it already exists:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fn_Get_List_ID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[Fn_Get_List_ID]

GO

3. Consequence of 2, always use a CREATE instruction (never use ALTER):

CREATE FUNCTION [dbo].[Fn_Get_List_ID]

(

@RowID INT

)

RETURNS INT

4. See my first post for what concerns dependencies and the order in which the script files must be arranged.

Have a nice day!

fstop Default port Points: 1482 More actions · Answer 9

Thanks for taking the time. Your suggestions are very much appreciated.

rf44 SSCommitted Points: 1515 More actions · Answer 10

rf44

SSCommitted

Points: 1515

May 6, 2014 at 4:38 pm

#1711301

You're welcome!