SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Script Creation of Multiple Stored Procedures


How to Script Creation of Multiple Stored Procedures

Author
Message
fstop
fstop
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 316
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
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7297 Visits: 2250
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
rf44
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 1093
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
CGSJohnson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2686 Visits: 1686
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
Nassan
Nassan
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 875
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
fstop
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 316
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
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60685 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
CGSJohnson
CGSJohnson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2686 Visits: 1686
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
rf44
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 1093
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
fstop
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 316
Thanks for taking the time. Your suggestions are very much appreciated.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search