3 Ways to Write Re-runnable SQL Scripts for Sproc

Previously spent a great deal talking about Re-runnable SQL Scripts for DDL and DML.

Our goal is the same, we are trying to create a suite of re-runnable and repeatable scripts to regenerate database from fresh. Previously, I deliberately left out the sproc part as I wanted to show some advance techniques.

1. Drop then Create 

We know for the fact that we cannot create the sproc if it already exists, the most basic technique is to drop the sproc in advance, so that the create script will not throw error.

If exists(select 1 from sys.procedures where Name = 'MyProc')
Begin
 Drop PROCEDURE [MyProc]
End
GO

CREATE PROCEDURE [dbo].[MyProc]

AS
BEGIN 
 Print '1'
END
GO

The drop and create is not a transitive relation. If there are any associated permissions or dependencies with the object, they get dropped with the object, and the create statement will not recreate them.

2. Create then Alter

A better approach is by creating a dummy object beforehand and use alter statement. Let's code first theory later.

If not exists(select 1 from sys.procedures where Name = 'MyProc')
Begin
 Exec('Create PROCEDURE [MyProc] AS SELECT 1')
End
GO

ALTER PROCEDURE [dbo].[MyProc]

AS
BEGIN
 Print '1'
END
GO

So far so good, and it is the typical answers that can get above 100 votes in stackoverflow easily.

While it does not lose object permissions or dependencies, I am having issues of losing the history of the objects. Let's take a look.

As you can see, this script is not 100% re-runnable. Modify_date is modified every time I run the script. I am losing the history of records and potentially bad for troubleshooting purpose.

3. Create then Alter on demand

Just to recap from previous. Here is our motto about re-runnable SQL scripts.

A SQL script is re-runnable if and only if the script will only have impact to the SQL server exactly once regardless how many times you run the script.

In order to conform this, we can only alter the sproc if it is dirty. Once again, let's code first.

If not exists(select 1 from sys.procedures where Name = 'MyProc')
Begin
 Exec('Create PROCEDURE [MyProc] AS SELECT 1')
 Print 'INFO - sproc MyProc created successfully'
End
GO

If not exists(Select 1 
  from sys.sql_modules 
  where object_id = (OBJECT_ID(N'MyProc')) 
  and CheckSum([Definition]) = '412750031')
Begin
Exec('
ALTER PROCEDURE [dbo].[MyProc]

AS
BEGIN
 Print ''1''
END')

 Print 'INFO - sproc MyProc updated successfully'
End
Else
Begin
 Print 'WARNING - sproc MyProc already updated.'
End
GO

Checksum

There is a few hurdles here. Firstly, we need to find out the checksum value of the sproc as part of the SQL script, BEFORE it is even created. That means we need to create the script first, run it, find out the checksum from the sproc, and then put it back in the script.

I would create the sproc in the database and find out the checksum by this query.

Select CheckSum([Definition]) as [CheckSum]
from sys.sql_modules
where object_id = (OBJECT_ID(N'MyProc'))

Dynamic SQL

As we cannot directly execute ALTER statements within an if-block, the only way out is using dynamic SQL. We will need to escape double quote in our dynamic SQL. This is often hard to read and creates a barrier for developers without strong SQL background.

Conclusion

Many developers would choose the Create then Alter method as it is easier to implement and mostly does the job.

Personally I prefer the more robust way of Create then Alter on demand method. We are able to receive instant feedback, and retaining record of history about the sproc. This information may sound trivial, but it could be vital for enterprise company with strict policy about live environment changes.

Although the learning curve is higher, but the result is cherish.