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.

Re-runnable SQL Scripts for DDL and DML

At some point of time in my career, I was in a role that I would prepare scripts for auto-refresh the database by running some SQL migration scripts, so that the database will match the latest binaries.

Concept

Here is my definition of 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.

This is a very important aspect if we are able to re-run some upgrading scripts without worrying about what-to-run and what-not-to-run during developments or deployments. Let alone continuous integration.

Implementation

There is a pattern in re-runnable SQL script. I will try to document as much as I could, but if you could learn the pattern instead of the code, then you can use it in any DDL (Data Definition Language) or DML (Data Manipulation Language).

Add column to existing table

If not exists(Select 1 from sys.columns where Name = N'PublishDate' and Object_ID = Object_ID(N'BlogPost'))
Begin
 Alter Table dbo.BlogPost 
 Add PublishDate datetime NULL

 Print 'INFO - Column PublishDate is added for table BlogPost successfully.'
End
Else
Begin
 Print 'WARNING - Column PublishDate already exists for table BlogPost.'
End
Go

Pay attention to the if-else logic, we simply put our DDL inside a if clause. Too simple?

If we run the above script twice, you would expect it not running the second time.

As we can see in the result pane, the DDL will not run the second time. 

Create table

If not exists(Select 1 from sys.tables where Name = N'Fit')
Begin

 CREATE TABLE [dbo].[Fit](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Code] [varchar](50) NOT NULL,
  [Name] [varchar](255) NOT NULL,
  [SubjectToAcl] [bit] NOT NULL CONSTRAINT [DF_Fit_SubjectToAcl]  DEFAULT ((0)),
  [LimitedToStores] [bit] NOT NULL CONSTRAINT [DF_Fit_LimitedToStores]  DEFAULT ((0))
 ) ON [PRIMARY]

 Print 'INFO - Table Fit created successfully.'
End
Else
Begin
 Print 'WARNING - Table Fit already exists.'
End
Go

Add PK

If not exists(Select 1 from sys.key_constraints where parent_object_id = Object_id('Fit'))
Begin
 ALTER TABLE dbo.Fit 
 ADD CONSTRAINT PK_Fit PRIMARY KEY CLUSTERED (Id) ON [PRIMARY]

 Print 'INFO - PK Fit added successfully.'
End
Else
Begin
 Print 'WARNING - PK Fit already exists.'
End
GO

Alter an existing column NULL to NOT NULL

In this scenario, we are changing a column from nullable to non-nullable. Things get a little tricky here

The first if statement act as a precaution to check if the column exists, because we cannot modify a column if it does not exist and it will throw error and causing the script to stop.

The second if statement depends on what you are modifying from. If we were changing nvarchar(50) to nvarchar(max), we would have written it differently. Also, depending on your technical requirement, you may want to force the column to be nvarchar(max) regardless, or it may not be an issue at all. It really DEPENDS!!

If exists(Select * from sys.columns where Name = N'Keyword' and Object_ID = Object_ID(N'SearchTerm'))
Begin
 If not exists(Select * from sys.columns where Name = N'Keyword' and Object_ID = Object_ID(N'SearchTerm') and Is_nullable = 0)
 Begin

  Alter Table dbo.SearchTerm 
  Alter column Keyword nvarchar(max) NOT NULL

  Print 'INFO - Column Keyword is altered for table SearchTerm successfully.'
 End
 Else
 Begin
  Print 'WARNING - Column Keyword is already non-nullable for table SearchTerm.'
 End
End
Else
Begin
 Print 'ERROR - Column Keyword does not exists in table SearchTerm.'
End
Go

For argument sake, should I also be checking if table exists when I am adding a column to a table too? That's right. It will throw error if we try to add a column to a non-existing table. In my technical situation though, I know I am expecting the table there, but that would be a nice pre-caution. Therefore, it DEPENDS on your technical structure.

Alter an existing column TEXT to VARCHAR(MAX)

Slightly different approach to the above. I join the system types table to find out the definition of the column.

If exists( Select * from sys.columns C 
   join sys.types T on T.system_type_id = C.system_type_id
   where C.Object_ID = Object_ID(N'MyTable')
   and C.Name = 'Description'
   and T.Name = 'text')
Begin

 Alter Table dbo.MyTable 
 Alter column [Description] varchar(max) NULL

 Print 'INFO - Column Description is altered for table MyTable successfully.'
End
Else
Begin
 Print 'WARNING - Column Description is already not text type for table MyTable.'
End
Go

Insert Data with Identity Insert

Notice you must have the keyword go after turning identity insert on/off, but since you can't have go inside begin/end, the statements will be outside of the if-block.

SET IDENTITY_INSERT [dbo].[TaxCategory] ON 
GO

If not exists (Select 1 from TaxRate where TaxCategoryId = 6 and CountryId = 6)
Begin

 INSERT [dbo].[TaxRate] ([TaxCategoryId], [CountryId], [StateProvinceId], [Zip], [Percentage], [StoreId]) VALUES (6, 6, 0, NULL, CAST(10.0000 AS Decimal(18, 4)), 0)
 
 Print 'INFO - Data for TaxRate.[TaxCategoryId] = 6 and TaxRate.[CountryId] = 6 inserted successfully.'
End
Else
Begin
 Print 'WARNING - Data for TaxRate.[TaxCategoryId] = 6 and TaxRate.[CountryId] = 6 already exists.'
End
GO

SET IDENTITY_INSERT [dbo].[TaxCategory] OFF
GO

Update Data - Exact match

If not exists (Select 1 from Country where Published = 1 and TwoLetterIsoCode = 'AU')
Begin

 Update Country
 Set Published = 1
 where TwoLetterIsoCode = 'AU'

 Print 'INFO - Data for Country.Published = 1 for TwoLetterIsoCode = ''AU'' updated successfully'
End
Else
Begin
 Print 'WARNING - Data for Country.Published = 1 for TwoLetterIsoCode = ''AU'' already updated.'
End
GO

Update Data - Multiple matches

The if clause is slightly different to above. It checks for if exists not equal to instead of if not exists equal to. That is because we want to update all the records as long as there is one or more than one record is returned when the where clause match.

If exists (Select 1 from Country where Published = 1 and TwoLetterIsoCode <> 'AU')
Begin

 Update Country 
 Set Published = 0
 where TwoLetterIsoCode <> 'AU'

 Print 'INFO - Data for Country.Published = 0 for TwoLetterIsoCode <> ''AU'' updated successfully'
End
Else
Begin
 Print 'WARNING - Data for Country.Published = 0 for TwoLetterIsoCode <> ''AU'' already updated.'
End
GO

Delete with conditions

If exists (Select 1 from [ShippingMethod] where [Name] = 'Australia Post, Regular Parcels')
Begin
 Delete [ShippingMethod]
 where [Name] <> 'Australia Post, Regular Parcels'

 Print 'INFO - Data for ShippingMethod <> ''Australia Post, Regular Parcels'' deleted successfully.'
End
Else
Begin
 Print 'INFO - Data for ShippingMethod <> ''Australia Post, Regular Parcels'' already deleted.'
End
GO

Conclusion

.Net developers might already notice I adopted some of the .Net logging technique in the print messages. That's right, I would look for warning or error (nothing fatal here) after running my script. The initial investment of a deep level logging would pay off easily in future troubleshooting. This is something that I would not do 7 years ago when I was not influenced by web developments.

I hope I have demonstrated enough as an appetizer of what re-runnable SQL scripts look like.

There is no hard rock science about what and how many checks to put in the where clause. It is up to the judgement call of the developer.