locked
Updating Multiple Stored Procedures with one file RRS feed

  • Question

  • Has anyone updated several existing stored procedures with one file?

    For example,

    I have the following...

    1. StoredProc1_R_Pr
    2. StoredProc2_R_Pr
    3. StoredProc3_R_Pr

    I need to make updates to all of the procedures.

    Instead of executing an update to each of the procs, I want to create a single .sql file and execute that file.

    Thank you for any help with this item.

    John

    Saturday, February 20, 2016 1:52 PM

Answers

  • First, I want to thank everyone that posted a reply to this post.

    I guess I need to work on describing the issue better.

    Just to let everyone know, I have solved my own issue.

    Simple updates or Alters to multiple stored procedures can be accomplished by adding additional ALTER phrases.

    Example:

    USE [Test_DB]
    GO
    /****** Object:  StoredProcedure [dbo].[TEST1_R_Pr]    
    Script Date: 2/23/2016 11:05:55 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    -- CODE SECTION: UPDATES TO STORED PROCEDURE, TEST1_R_Pr
    
    ALTER Procedure [dbo].[TEST1_R_Pr] ()
    
    AS
    
    select * from sometable
    where sometable.column = 'foobar'
    
    GO
    
    -- CODE SECTION: UPDATES TO STORED PROCEDURE, TEST2_R_Pr
    
    ALTER Procedure [dbo].[TEST2_R_Pr] ()
    
    AS
    
    select * from sometable2
    where sometable2.column2 = 'barfoo'
    
    GO
    
    -- CODE SECTION: UPDATES TO STORED PROCEDURE, TEST3_R_Pr
    
    ALTER Procedure [dbo].[TEST3_R_Pr] ()
    
    AS
    
    select * from sometable3
    where sometable3.column3 = 'theend'
    
    GO
    
    .
    .
    .
    -- and so on...

     

    It's Not Who I Am Underneath, But What I Do That Defines Me.

    • Marked as answer by jmcleod3 Tuesday, February 23, 2016 8:03 PM
    Tuesday, February 23, 2016 8:02 PM

All replies

  • Keep your single files.

    Use a control file and run in SQLCMD mode.

    Saturday, February 20, 2016 2:26 PM
  • Hi,

    I use to create different procedures in all my database. So I have  a script that could help you. Of course the script was made for creating procedures but with small changes (instead of CREATE use ALTER and so on) can be exactly what you're looking for.

    If you need more help please let me know
    Bellow the code:

    	DECLARE @sql NVARCHAR(MAX)
    	SELECT @sql = '
    	IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
    	BEGIN 
    	USE ? 
    	EXEC(''
    	CREATE PROCEDURE [dbo].[FIND_SPECIFIC_TEXT_IN_SCRIPTS]  
    	@search_text NVARCHAR(MAX) 
    	WITH ENCRYPTION
    	AS 
    	BEGIN
    		SET NOCOUNT ON;
    			SELECT DISTINCT
    				o.name AS Object_Name,o.type_desc
    				FROM sys.sql_modules        m 
    					INNER JOIN sys.objects  o ON m.object_id=o.object_id
    				WHERE m.definition Like ''''%''''+@search_text+''''%''''
    				ORDER BY 2,1
    	END
    	'') END'
    
    	PRINT @sql
    	EXEC sp_MSforeachdb @sql
    PLease let me know if everything was entirely understandable.


    Thanks in advance, Ciprian LUPU




    • Edited by Ciprian Lupu Saturday, February 20, 2016 2:46 PM
    Saturday, February 20, 2016 2:39 PM
  • I did not really understand what do you mean as updating the procedures (altering?). If you meant to execute the procedures all together, easy...SSIS -->Execute Task-->in cascade put all the procedures you want. After that you can run the package manually when you need or you can schedule it on SQL Agent. Believe me, it is very easy.

    Please mark as answer if this post helped you

    Saturday, February 20, 2016 3:31 PM
  • Like I said,

    I have several (>20) procs and each of them needs to be updated.  

    We are currently upgrading from SQL Server 2010 to SQL Server 2014 and table columns have been updated to accommodate deprecated data types. 

    Instead of executing each of these procs one at a time maybe the question should have been, has anyone executed a single file to ALTER multiple stored procedures?  

    Sorry for the confusion.

    John

    Saturday, February 20, 2016 4:14 PM
  • Sorry, I still struggle to understand what you mean. You got SP1 and SP2 and you need to alter both..the question is what kind of alter? You want alter the code so you need to ALTER the procedure writing the corrective code for SP1 and SP2..or not? You can write everything in one query editor saving it as .sql or you can use a SSIS package but you, however, must write the alter code..probably I miss something.
    Saturday, February 20, 2016 4:41 PM
  • Ulimtately, this boils down to how you organise your source code.

    Like any other source code, code for stored procedures should kept in a version control system (TFS, Subversion etc). How you organise them there, is your choice. My preference is to have one SP per file, and the name of the file matches the SP name. But in a project that I worked with many years ago, we had all stored procedures that supported the same form in a single file.

    The CREATE/ALTER thing is a little cumbersome for people. A simple approach is to do:

    IF object_id('my_sp') IS NULL
       EXEC('CREATE PROCEDURE my_sp AS PRINT 1')
    go
    ALTER PROCEDURE my_sp @par1 int,
    -- Procedure body follows here.
    go

    That is, if the procedure does not exist, create a dummy, and the actual procedure text always has ALTER.

    There are also tools out there that help you to build database code, and they may perform the above on their own in some variation or another. But they may also impose restriction, like requiring that each procedure to be in their own file. Then again, they also typically offer a mechanism to deploy all changes, either directly or build install packages.

    Here is a few such tools - all three very different in nature:

    Red Gate's SQL Compare - compares the target database with another database or files on disk or under version control. Very popular.

    SQL Server Data Tools SSDT - A free add-on to Visual Studio that gives you a very versatile environment.

    AbaPerls - my own offering which we use to build the system I work with. Packed with functionality, but all is command-line, no GUI. It's available on http://www.sommarskog.se/AbaPerls/index.html.

    Now, whether I actually answered your question, or went off a tangent, remains to see. But the short answer to your question is: just do it!

    • Proposed as answer by Eric__Zhang Monday, February 22, 2016 2:34 AM
    Saturday, February 20, 2016 5:35 PM
  • First, I want to thank everyone that posted a reply to this post.

    I guess I need to work on describing the issue better.

    Just to let everyone know, I have solved my own issue.

    Simple updates or Alters to multiple stored procedures can be accomplished by adding additional ALTER phrases.

    Example:

    USE [Test_DB]
    GO
    /****** Object:  StoredProcedure [dbo].[TEST1_R_Pr]    
    Script Date: 2/23/2016 11:05:55 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    -- CODE SECTION: UPDATES TO STORED PROCEDURE, TEST1_R_Pr
    
    ALTER Procedure [dbo].[TEST1_R_Pr] ()
    
    AS
    
    select * from sometable
    where sometable.column = 'foobar'
    
    GO
    
    -- CODE SECTION: UPDATES TO STORED PROCEDURE, TEST2_R_Pr
    
    ALTER Procedure [dbo].[TEST2_R_Pr] ()
    
    AS
    
    select * from sometable2
    where sometable2.column2 = 'barfoo'
    
    GO
    
    -- CODE SECTION: UPDATES TO STORED PROCEDURE, TEST3_R_Pr
    
    ALTER Procedure [dbo].[TEST3_R_Pr] ()
    
    AS
    
    select * from sometable3
    where sometable3.column3 = 'theend'
    
    GO
    
    .
    .
    .
    -- and so on...

     

    It's Not Who I Am Underneath, But What I Do That Defines Me.

    • Marked as answer by jmcleod3 Tuesday, February 23, 2016 8:03 PM
    Tuesday, February 23, 2016 8:02 PM
  • You can use the Generate Scripts feature from SSMS to generate the stored procedures you want to modify. You can include the option to Check for object existence from the Advanced tab.

    Right click on your database name>>tasks>>Generate Scripts>> Select specific database objects>> you can choose the Stored Procedures you are interested  >>Next>> Click on Advanced tab to set different settings >>Ok

    >>Next >> (you will have a full script generated by SSMS ) you can modify this file to include changes you want to make to each stored procedure. You can execute this file to your target database. With Check object existence code, you can run the script multple time without issue to whether you have the stored procedure in your target database.

    With ALter procedure only, the target database must have this stored procedure already.

    Tuesday, February 23, 2016 9:13 PM