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.