Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Script Creation of Multiple Stored Procedures Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 4:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
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
Post #1567738
Posted Monday, May 5, 2014 11:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1567781
Posted Tuesday, May 6, 2014 3:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 83, Visits: 1,013
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!


Post #1567844
Posted Tuesday, May 6, 2014 8:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 1,546, Visits: 1,334
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
Post #1567969
Posted Tuesday, May 6, 2014 1:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:01 PM
Points: 36, Visits: 716
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
Post #1568169
Posted Tuesday, May 6, 2014 2:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
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
Post #1568181
Posted Tuesday, May 6, 2014 2:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
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)
Post #1568187
Posted Tuesday, May 6, 2014 2:20 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 1,546, Visits: 1,334
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
Post #1568188
Posted Tuesday, May 6, 2014 2:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 83, Visits: 1,013
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!
Post #1568190
Posted Tuesday, May 6, 2014 2:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
Thanks for taking the time. Your suggestions are very much appreciated.
Post #1568194
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse