2

I have solution for Stored Procedures creation exactly like in this answer https://stackoverflow.com/a/15171900.

I am running

Sql(Properties.Resources.Create_sp_DoSomething);

in my Initial DbMigration.

My sql scripts have code to first drop existing SP and then create new updated SP. So whene I run 

Sql(Properties.Resources.Create_sp_DoSomething);

in new DbMigration, and logic inside SP is changed everything works fine.

The problem arise when I want to update Stored Procedure with columns (lets say IsActive) which were added to model in later commits, and I am updating without existing DB (so new DB is created). Then It fails with

Invalid column name 'IsActive'.

Any good solution to that other than removing all existing calls to

Sql(Properties.Resources.Create_sp_DoSomething);

and have it only in newest DbMigration.

Community
  • 1
  • 1
Krzysztof Morcinek
  • 1,011
  • 13
  • 42

1 Answers1

4

Separate your stored procedures from your model creation by doing your stored procedure updates in the migration Seed() method which runs after all the migrations:

context.Database.ExecuteSqlCommand(sp_DoSomething);

Since this runs with every update-database, you will need to make the script idempotent by adding an existance check to the start of Create_sp_DoSomething:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_DoSomething')
BEGIN
    DROP PROCEDURE sp_DoSomething
END
Steve Greene
  • 12,029
  • 1
  • 33
  • 54
  • 1
    When you make this script idempotent (which is needed) you need to add *GO* statements between Drop and Create SP, and this approach with *ExecuteSqlCommand()* will require something like https://smehrozalam.wordpress.com/2009/05/12/c-executing-batch-t-sql-scripts-with-go-statements/ – Krzysztof Morcinek Jul 26 '16 at 09:38