如何编写可在.NET Core数据库迁移中无编译错误运行的带参动态存储过程

核心错误原因 #

1. GO命令不被迁移工具支持：GO是SSMS专属的批处理分隔符，.NET Core迁移工具会把整个脚本当作单条T-SQL执行，GO会被识别为无效语法，触发报错。
2. 动态SQL变量长度不足：@SQL定义为nvarchar(500)，但拼接后的SQL远超过这个长度，会被截断，导致后续语法混乱（比如BEGIN语句位置错误）。
3. 动态SQL中的表别名错误：搜索条件里误用了未定义的别名[e]，实际应该是用户表的别名[u]。

修正步骤及脚本 #

步骤1：移除GO命令，合并批处理逻辑 #

用T-SQL原生逻辑替代GO的批处理作用，避免迁移工具识别无效语法。

步骤2：扩大动态SQL变量的长度 #

将@SQL的类型改为nvarchar(max)，确保拼接后的完整SQL不会被截断。

步骤3：修正表别名错误 #

把搜索条件里的[e]改为用户表的正确别名[u]。

修正后的完整脚本 #

IF EXISTS(SELECT * FROM sys.objects WHERE NAME = 'users' AND TYPE = 'P')
BEGIN
    DROP PROCEDURE [users];
END;

CREATE PROCEDURE [users] 
    (@Username nvarchar(500),
     @Progress nvarchar(500),
     @StartDate nvarchar(500),
     @EndDate nvarchar(500),
     @DisplayName nvarchar(500),
     @CourseName nvarchar(500),
     @Search nvarchar(500),
     @Sort nvarchar(500),
     @Page int,
     @PageSize int) 
AS 
BEGIN
    SET NOCOUNT ON;
    SET ANSI_NULLS ON;
    SET ANSI_PADDING ON;
    SET ANSI_WARNINGS ON;
    SET ARITHABORT ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT Off;
    SET QUOTED_IDENTIFIER ON;
 
    DECLARE @offsetcount nvarchar(500);
    DECLARE @pagecount nvarchar(500);
    DECLARE @SQL nvarchar(max); -- 改为max长度避免截断
    DECLARE @SortFilter nvarchar(500);

    SET @offsetcount = convert(nvarchar,(@PageSize * (@Page - 1)));
    SET @pagecount = convert(nvarchar,(@PageSize));


SET @SQL = 'SELECT [u].[Name], [u].[Username],
           SUM(CASE WHEN [c].[Status] = ''Published'' THEN 1 ELSE 0 END) AS Total,
           sum(case when [ec].[UserCourseStatus] = ''In Progress'' AND [c].[Status] = ''Published'' then 1 else 0 end) AS Pending,
           sum(case when [ec].[UserCourseStatus] = ''Completed'' AND [c].[Status] = ''Published'' then 1 else 0 end) AS Completed
           FROM (([db].[UserCourse] As [ec]
           INNER JOIN [db].[User] As [u]
           ON [u].[Id] = [ec].[UserId])
           INNER JOIN [db].[Course] As [c]
           ON [c].[Id] = [ec].[CourseId])
           WHERE ([u].[StatusCode] = 2 OR [u].[StatusCode] IS NULL) AND [c].[Status] = ''Published''';

IF @DisplayName IS NOT NULL AND LEN(@DisplayName) > 0 
   SET @SQL = @SQL + ' AND [u].[Name] = ''' + @DisplayName + '''';
IF @Progress IS NOT NULL AND LEN(@Progress) > 0 
   SET @SQL = @SQL + ' AND [ec].[UserCoursestatus] = ''' + @Progress + '''';
IF @StartDate IS NOT NULL AND LEN(@StartDate) > 0 
   SET @SQL = @SQL + ' AND ([ec].[DateFirstStarted] >= ''' + @StartDate + ''' AND [ec].[DateFirstCompleted] <= ''' + @EndDate + ''')';
IF @CourseName IS NOT NULL AND LEN(@CourseName) > 0 
   SET @SQL = @SQL + ' AND [c].[Name] = ''' + @CourseName + '''';
IF @Search IS NOT NULL AND LEN(@Search) > 0 
   SET @SQL = @SQL + ' AND ([u].[Name] like ''%' + @Search + '%'' or [u].[Username] like ''%' + @Search + '%'')'; -- 修正别名错误

SET @SortFilter = CASE
     @Sort
     WHEN 'UserAsc' THEN '[u].[Name] ASC'
     WHEN 'UserDesc' THEN '[u].[Name] DESC'
     WHEN 'In Progress' THEN '[ec].[UserCourseStatus] DESC'
     WHEN 'Completed' THEN '[ec].[UserCourseStatus] ASC'
     WHEN 'CreatedDesc' THEN '[ec].[DateFirstStarted] DESC, [ec].[DateFirstCompleted] DESC'
     WHEN 'CreatedAsc' THEN '[ec].[DateFirstStarted] ASC, [ec].[DateFirstCompleted] ASC'
     ELSE '[u].[Name] DESC'
  END;

SET @SQL = @SQL + ' GROUP BY [u].[Name], [u].[Username]';
SET @SQL = @SQL + ' Order BY ' + @SortFilter;

IF @Page IS NOT NULL AND @Page != 0 
   SET @SQL = @SQL + ' OFFSET ' + @offsetcount + ' ROWS FETCH NEXT ' + @pagecount + ' ROWS ONLY';
ELSE 
 SET @SQL = @SQL + ' OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';

EXEC sp_executesql @SQL; -- 推荐用sp_executesql替代EXEC，提升安全性
END;

额外建议 #

• 避免直接拼接用户输入到动态SQL，容易引发SQL注入风险，建议改用sp_executesql的参数化查询方式。
• 迁移脚本中只使用标准T-SQL语法，不要用SSMS专属命令（如GO、:setvar等）。

内容的提问来源于stack exchange，提问作者Brian Tananganji Manda