Share via

SQL Server: Altering stored procedures using Fluent Migrator

  • Article

Scenario

You have to add a column to an existing table. The stored procedures that work with the table have to be modified to account for the new column.

You make the changes to the table in a new migration class Up method. On that method you will also have to change the stored procedure in the DB to account for the new column. Here the nightmare begins: if you change your embedded stored procedure sql file to ALTER instead of CREATE, you will get an exception when running the migration for the first time, as the Stored Procedure you are trying to ALTER doesn't exist yet.

If you keep CREATE in the embedded sql file and DROP the existing stored procedure before executing it your Migrate Up would work, but not your Migrate Down, because you will be trying to create stored procedure that references a column that does not exist yet.

Solution

The way I resolved this was by keeping all the versions of the stored procedure sql files in the project and associating them with the Migration where they get executed by adding the Migration version to the sql file name.

Example

Here is an example:

  • table named NewKey. 
  • add a column to that table named KeyID.
  • a stored procedure in the DB and in my FM project called GetNewKey. That stored procedure does not reference KeyID, because the column didn't exist.
  • make a copy of the sql file GetNewKey.sql, containng the CREATE statement that creates the GetNewKey stored procedure, and named that copy GetNewKey_201407091803.sql 

Here is the migration code that does the rest:

01.[Migration(201407091803)]
02.public class  NewKeyDatabaseMigration_201407091803 : Migration
03.{
04.    /// <summary>
05.    ///     DB Changes
06.    /// </summary>
07.    public override  void Up()
08.    {
09.        // Create new column
10.        Alter.Table("NewKey")
11.            .AddColumn("KeyID").AsInt64().NotNullable();
12. 
13.        // Drop previous SPs
14.        Execute.Sql("DROP PROCEDURE [nks].[GetNewKey]");
15. 
16.        // Recreate SP using new scripts
17.        Execute.EmbeddedScript("GetNewKey_201407091803.sql");
18.    }
19. 
20.    /// <summary>
21.    ///     DB Changes Rollback
22.    /// </summary>
23.    public override  void Down()
24.    {
25.        Delete.Column("KeyID")
26.            .FromTable("NewKey");
27. 
28.        // Drop current SPs
29.        Execute.Sql("DROP PROCEDURE [nks].[GetNewKey]");
30. 
31.        // Recreate SPs using previous script
32.        Execute.EmbeddedScript("GetNewKey.sql");
33. 
34.    }
35.}

For convenience we used a DateTime YYYYMMDDHHMM time stamp as migration version: 201407091803