Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How do you create or update stored procedures.

G’day,

Running object creation scripts into other environments is a big part of most DBA’s work – especially when a new system is being built in an agile manner. Stored procedures get created and altered all the time.

Very often I used to see code like this

IF EXISTS
(
    SELECT * FROM sys.procedures P
    JOIN sys.schemas S
    ON P.[schema_id] = S.[schema_id]
    WHERE
        P.[type] = 'P'
    AND
        P.[name] = 'MyTestProcedure'
    AND
        S.[name] = 'dbo'
)
BEGIN
    DROP PROCEDURE dbo.MyTestProcedure;
END
GO
CREATE PROCEDURE dbo.MyTestProcedure
AS
BEGIN
    PRINT 'dbo.MyTestProcedure';
END
GO

One of the things that I like about this code is that it is re-usable – it never errors. But one of the big draw-backs is that it drops an object from the database,  and along with the dropping of the object will go any security that has been placed on that object.

What I’d prefer to see is code that maintains the object – i.e just altered the object.

To do this we need the algorithm to be of the form

  • If the stored procedure does not exist then CREATE it.
  • If the stored procedure does exist then ALTER it

This could potentially be a bit tricky – mostly because CREATE PROCEDURE has to be the first statement in the batch. If it is not then we’ll see an error message simlar to the following

Msg 111, Level 15, State 1, Procedure MyTestProcedure,
Line 9 ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.


But there is a way to get around this. We need to use a way of dynamically creating the stored procedure and we just tweak the algorithm a little bit to work as follows.

  • If the stored procedure does not exist then CREATE it.
  • Now simply ALTER the stored procedure – as one way or another it now definitely exists.

Which gives us code similar to the following

IF NOT EXISTS
(
    SELECT * FROM sys.procedures P
    JOIN sys.schemas S
    ON P.[schema_id] = S.[schema_id]
    WHERE
        P.[type] = 'P'
    AND
        P.[name] = 'MyTestProcedure'
    AND
        S.[name] = 'dbo'
)
BEGIN
    PRINT 'Stored procedure ''dbo.MyTestProcedure'' does not exist - about to create it';
    EXECUTE('CREATE PROCEDURE dbo.MyTestProcedure AS PRINT ''dbo.MyTestProcedure SP''');
    PRINT 'Stored procedure ''dbo.MyTestProcedure'' created.';
END;
GO
ALTER PROCEDURE dbo.MyTestProcedure
    /*Replace params with your custom params*/
    @PARAM1 INT
AS
BEGIN
    /*Replace body of procedure with you custom code*/
    PRINT 'Altered Procedure';
END;
GO

The nice thing that I like about this code is that

  • It is re-usable and can be run without error regardless of whether the object exists or not.
  • Because the above point is true, only 1 version of the file needs to be made, different scripts for ALTERing and CREATEing are never need – just keep one file in your source control system.

Have a nice day.

cheers

Martin.

Comments

Posted by Steve Jones on 4 May 2011

That's a nice trick. I see a Connect item to implement CREATE OR REPLACE as DDL, but this is a good way to get around it.

I also keep the security with the object code. So all my stored proc code in source control has the GRANT statements with it.

Posted by Chris Harshman on 4 May 2011

I've been trying to get the developers where I work to use this kind of "non destructive" method, especially since they don't include the GRANTs with the stored proc code in source control.

The CREATE OR REPLACE syntax is something that Oracle uses, and is one of the things I miss from my Oracle days.  But with each release of SQL Server that list grows shorter.

Posted by Martin Catherall on 4 May 2011

A CREATE OR REPLACE DDL statement would be welcomed by a lot of people, I'm sure.

Chris, we keep our security statements separate to our objects so this method works well for us.

Steve, - glad you like it -I understand that some people keep the security statements together with the object script, my personal experience has shown me that sometimes the security changes and the sp script does not get updated, but I guess that's the process that's not being followed.

Posted by David C on 4 May 2011

This is exactly what we switched to (from the old DROP/CREATE method) a while ago. Permissions issues are now virtually non-existant. As an aside, you don't need anything after the "AS" either, so "EXEC('CREATE PROC dbo.blah AS ')" works just fine too.

Posted by Martin Catherall on 4 May 2011

Thanks for the comment David. That's an interesting piece of information that you bring up, cheers martin.

Leave a Comment

Please register or log in to leave a comment.