EF Core生成含存储过程的幂等迁移脚本运行报错的解决方法

方案一：手动编写幂等的动态SQL创建语句 #

修改迁移中Up()方法的存储过程创建代码，用动态SQL实现幂等性，确保SQL Server能合法执行：

var sp = @"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyStoredProcedureFromMigration]') AND type = N'P')
BEGIN
    EXEC sp_executesql N'
        CREATE PROCEDURE [dbo].[MyStoredProcedureFromMigration]
        AS
        BEGIN
            SET NOCOUNT ON;
            SELECT * FROM Students WHERE FirstName LIKE ''%dummy%''
        END
    '
END";

migrationBuilder.Sql(sp);

这种写法能规避SQL Server的语法限制——即使EF Core将整个迁移操作包裹在幂等检查的IF块中，内部的动态SQL也能正常运行，不会触发"PROCEDURE关键字附近语法错误"。

如果后续需要修改存储过程，可以改成先判断存在则ALTER、不存在则CREATE的逻辑：

var sp = @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyStoredProcedureFromMigration]') AND type = N'P')
BEGIN
    EXEC sp_executesql N'
        ALTER PROCEDURE [dbo].[MyStoredProcedureFromMigration]
        AS
        BEGIN
            SET NOCOUNT ON;
            -- 修改后的逻辑
            SELECT * FROM Students WHERE FirstName LIKE ''%updated%''
        END
    '
END
ELSE
BEGIN
    EXEC sp_executesql N'
        CREATE PROCEDURE [dbo].[MyStoredProcedureFromMigration]
        AS
        BEGIN
            SET NOCOUNT ON;
            SELECT * FROM Students WHERE FirstName LIKE ''%dummy%''
        END
    '
END";

migrationBuilder.Sql(sp);

方案二：使用EF Core 7.0+的原生存储过程支持 #

如果项目基于EF Core 7.0或更高版本，可使用EF Core提供的CreateStoredProcedure扩展方法，EF会自动生成合法的幂等脚本，无需手动处理动态SQL：

migrationBuilder.CreateStoredProcedure(
    "[dbo].[MyStoredProcedureFromMigration]",
    procedure => procedure
        .WithResultColumn<int>("Id")
        .WithResultColumn<string>("FirstName")
        // 添加其他需要的结果列
        .SqlBody(@"
            SET NOCOUNT ON;
            SELECT * FROM Students WHERE FirstName LIKE '%dummy%'
        ")
);

方案三：流水线中添加脚本预处理步骤 #

若无法修改迁移代码，可在Azure DevOps流水线中生成脚本后，添加PowerShell任务自动修复存储过程的语法：

在生成迁移脚本的任务后添加以下步骤：

- task: PowerShell@2
  displayName: 'Fix stored procedure syntax in migration script'
  inputs:
    targetType: 'inline'
    script: |
      $scriptPath = "$(System.ArtifactsDirectory)/script.sql"
      $content = Get-Content $scriptPath -Raw
      
      # 匹配被迁移检查IF块包裹的CREATE PROCEDURE语句
      $pattern = '(IF NOT EXISTS \(SELECT \* FROM __EFMigrationsHistory WHERE MigrationId = ''[^'']+''\))\s*BEGIN\s*(CREATE PROCEDURE [\s\S]+?)END'
      $replacement = '$1
      BEGIN
          EXEC sp_executesql N''$2''
      END'
      
      $updatedContent = $content -replace $pattern, $replacement
      Set-Content $scriptPath $updatedContent -Encoding UTF8

该脚本会自动将幂等脚本中被IF块包裹的CREATE PROCEDURE语句，替换为通过sp_executesql执行的动态SQL，修复语法错误。

内容的提问来源于stack exchange，提问作者Reinder Wit