Click here to monitor SSC
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
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 314
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
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
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 1088
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 1682
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 Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 849
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
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 314
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16548 Visits: 17004
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 Moden's 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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 1682
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
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 1088
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
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 314
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