Database deployment scripting with Dynamic SQL

Bob Blackburn

2014-08-01

Dynamic SQL is most often associated with online applications. This is because you are processing unknowns and coding for every possible scenario is impossible. Dynamic SQL can help you process unknown inputs. However, it also opens you up to SQL Injection. A very serious problem; but, out of the scope of this blog post (See additional reading). This article will focus on a few cases where Dynamic SQL will solve some batch scripting problems.

Database deployments (vs code deployments) can have their own set of problems. We often have to maintain multiple environments at different versions and upgrade scripts from many versions to many versions. The use of DACPACs in SQL Server can automate a great portion of the deployment process; but, there are some tasks (Data conversion, schema changes with data loss) that must be kept out of the DACPAC. Maintaining numerous pre and post deployment scripts can become a full time endeavor and the likelihood of errors goes up. It would be best to automate the process and maintain only a handful of scripts.

We will look at two specific issues that arise when trying to automate versioning in a script. They are Batch Commands and Delayed Resolution.

When scripting database upgrades, the use of version numbers controls the logic. This causes a few extra steps in certain cases (like no use of GO statement inside an IF statement). Also, creating procedures based on upgrade path because the CREATE PROCEDURE statement must be the only statement in a batch. Luckily Dynamic SQL gets around this. As an added bonus, we can also use a different database inside Dynamic SQL without being stopped by the CREATE statement being the only statement in the batch rule.

For the SQL command, we will create an EndLine character to allow for easier reading. Using strings with embedded quotes also takes a little time to get use to if you are new to Dynamic SQL.

-- Uses two databases DEMO1 and DEMO2

USE DEMO1

GO

Declare @TargetVersion char(5) = '03.01'

       ,@PreviousVersion char(5) = '03.00';

IF @TargetVersion > @PreviousVersion

Begin

      DECLARE @EndLine NCHAR(2) = NCHAR(13) + NCHAR(10);

      DECLARE @TSQL NVARCHAR(4000);

      SET @TSQL =

            N'Create Procedure PrintHello' + @EndLine +

            N'AS' + @EndLine +

            N'Begin' + @EndLine +

            N'Print(''''Hello World!'''')' + @EndLine +

            N'End' 

            SET @TSQL = 'USE Demo2; EXEC sp_executesql N''' + @TSQL + '''';

            Begin Try

                  EXEC  (@TSQL)

            End Try

            Begin Catch

                  Print('Procedure PrintHello already exists')

            end Catch

END -- IF @TargetVersion > @PreviousVersion

USE DEMO2

GO

EXEC PRINTHELLO

GO

The output is simply ‘Hello World!’. But, we were able to get around a few batch rules with Dynamic SQL.

This may be the more frequent need for Dynamic SQL in batch scripting. Changes to the Schema often create the need to back fill columns. We have to add them as null, back fill, and then make NOT NULL to allow the DACPAC to deploy and create indexes on columns that are not null. The catch is the column does not exist yet so your script fails validation. Another feature of SQL is an UPDATE statement won’t recognize a new column unless a GO is executed after the ALTER TABLE. This forces us to create multiple IF statements to control the script. For this example, we will declare and initialize the variables. After each batch (GO) you will have to reset the variables from table.

Declare @TargetVersion char(5) = '03.01'

       ,@PreviousVersion char(5) = '03.00';

CREATE TABLE [Customer](

      [CustomerID] [int] IDENTITY(1,1) NOT NULL,

      [FirstName] [nvarchar](30) NOT NULL,

      [LastName] [nvarchar](30) NOT NULL,

 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([CustomerID] ASC) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO [dbo].[Customer]

           ([FirstName]

           ,[LastName])

     VALUES

           ('Code'

           ,'Monkey')

If @TargetVersion > @PreviousVersion

Begin

      ALTER TABLE Customer ADD CustomerName nvarchar(60)  null;

end

GO

Declare @TargetVersion char(5) = '03.01'

       ,@PreviousVersion char(5) = '03.00';

If @TargetVersion > @PreviousVersion

Begin

      DECLARE @EndLine NCHAR(2) = Nchar(13) + Nchar(10);

      DECLARE @sql NVARCHAR(4000); 

      SET @sql =

            N'UPDATE Customer' + @EndLine +

            N'set CustomerName = FirstName + '' '' + LastName;'

      EXEC (@SQL)

 

End

ALTER TABLE Customer ALTER COLUMN CustomerName nvarchar(60) not null;

GO

select *

  from Customer

With these examples of Dynamic SQL in batch scripting, you can reduce the amount of scripts you need to maintain and control the upgrade process as needed.