SQL Server自定义分页存储过程：完善zsp_GetUsersAdministratorPanel存储过程咨询

没问题，我来帮你补全这个用于管理员面板的用户数据分页存储过程，它会完整实现分页、动态排序和多字段搜索功能，还加入了SQL注入防护：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[zsp_GetUsersAdministratorPanel] 
(
    @StartRowIndex INT, 
    @MaxRows INT, 
    @OrderByField NVARCHAR(200), 
    @Asc BIT, 
    @SearchValue NVARCHAR(200)
)
AS
BEGIN
    SET NOCOUNT ON;

    -- 定义允许排序的字段白名单，防止SQL注入
    DECLARE @AllowedSortFields TABLE(FieldName NVARCHAR(200))
    INSERT INTO @AllowedSortFields VALUES ('UserId'), ('FirstName'), ('LastName'), ('Email')

    -- 处理排序字段：如果不在白名单内，默认用UserId排序
    DECLARE @ValidOrderByField NVARCHAR(200) = 
        (SELECT TOP 1 FieldName FROM @AllowedSortFields WHERE FieldName = @OrderByField)
    IF @ValidOrderByField IS NULL
        SET @ValidOrderByField = 'UserId'

    -- 处理排序方向
    DECLARE @SortDirection NVARCHAR(4) = CASE WHEN @Asc = 1 THEN 'ASC' ELSE 'DESC' END

    -- 构建搜索条件
    DECLARE @WhereQuery NVARCHAR(MAX) = ''
    IF LEN(@SearchValue) > 0
    BEGIN
        SET @WhereQuery = ' WHERE (FirstName LIKE ''%'' + @SearchValue + ''%'' 
                                OR LastName LIKE ''%'' + @SearchValue + ''%'' 
                                OR Email LIKE ''%'' + @SearchValue + ''%'')'
    END

    -- 构建动态SQL语句，使用CTE实现分页
    DECLARE @Sql NVARCHAR(MAX) = 
        'WITH PaginatedUsers AS (
            SELECT 
                UserId, 
                FirstName, 
                LastName, 
                Email,
                ROW_NUMBER() OVER (ORDER BY ' + @ValidOrderByField + ' ' + @SortDirection + ') AS RowNum
            FROM Users ' + @WhereQuery + '
        )
        SELECT UserId, FirstName, LastName, Email
        FROM PaginatedUsers
        WHERE RowNum BETWEEN @StartRowIndex AND (@StartRowIndex + @MaxRows - 1)'

    -- 执行动态SQL，传入参数避免注入
    EXEC sp_executesql @Sql, 
        N'@StartRowIndex INT, @MaxRows INT, @SearchValue NVARCHAR(200)',
        @StartRowIndex = @StartRowIndex, 
        @MaxRows = @MaxRows, 
        @SearchValue = @SearchValue
END
GO

关键功能说明： #

• SQL注入防护：
  • 对排序字段做了白名单验证，只允许指定的字段（UserId/FirstName/LastName/Email）用于排序，避免恶意输入导致注入风险
  • 使用sp_executesql传入参数，而不是直接拼接搜索值，彻底杜绝搜索条件的注入问题
• 搜索功能：
  • 当@SearchValue不为空时，会模糊匹配用户的FirstName、LastName和Email字段，满足多维度搜索需求
• 动态排序：
  • 根据@OrderByField和@Asc参数动态调整排序规则，若传入的排序字段不在白名单内，默认使用UserId排序
• 分页实现：
  • 借助CTE和ROW_NUMBER()函数实现分页，筛选行号在@StartRowIndex到@StartRowIndex+@MaxRows-1范围内的记录，符合常规分页逻辑

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