萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

動態 SQL (sp_executesql) 實戰指南:參數化、型別限制、通用分頁範例

核心三要素

  1. @SQL:動態組裝的 T-SQL 字串,**必須為 Unicode 型別** (NVARCHAR/NCHAR/NTEXT),否則報錯「程序需要類型為 'ntext/nchar/nvarchar' 的參數」。
  2. @Params:參數定義字串,格式 N'@Param1 Type1, @Param2 Type2...',**同樣必須為 Unicode**。
  3. 參數值傳遞:EXEC sp_executesql @SQL, @Params, @Param1 = Value1, @Param2 = Value2...,名稱**必須對應**。

動態表名/欄位名處理

DECLARE @TableName SYSNAME = N'Orders';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE Id = @Id';
-- 必須用 QUOTENAME 防注入,且表名不可參數化,只能字串拼接

通用分頁預存程序範例 (SQL 2000/2005 相容)

CREATE PROC usp_UniversalPager
    @TableName NVARCHAR(50),
    @ReturnFields NVARCHAR(200) = N'*',
    @PageSize INT = 50,
    @PageIndex INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(200);

    -- SQL 2005+ 使用 ROW_NUMBER (此處示範 2000 相容雙重 TOP)
    DECLARE @Start INT = (@PageIndex - 1) * @PageSize;
    SET @SQL = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + N' ' + @ReturnFields +
               N' FROM (SELECT TOP ' + CAST(@Start + @PageSize AS NVARCHAR) + N' ' + @ReturnFields +
               N' FROM ' + QUOTENAME(@TableName) + N' ORDER BY Id) AS T ORDER BY Id DESC';

    PRINT @SQL;  -- 除錯用
    EXEC sp_executesql @SQL;  -- 無參數版本
END;

進階:帶條件篩選的動態分頁

CREATE PROC usp_DynamicPager
    @TableName NVARCHAR(50),
    @Fields NVARCHAR(500) = N'*',
    @WhereClause NVARCHAR(MAX) = N'',
    @OrderBy NVARCHAR(200) = N'Id',
    @PageSize INT = 20,
    @PageIndex INT = 1,
    @TotalRows INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(200) = N'@TotalRows INT OUTPUT';

    -- 1. 總筆數
    SET @SQL = N'SELECT @TotalRows = COUNT(*) FROM ' + QUOTENAME(@TableName) +
               CASE WHEN @WhereClause = N'' THEN N'' ELSE N' WHERE ' + @WhereClause END;
    EXEC sp_executesql @SQL, @Params, @TotalRows OUTPUT;

    -- 2. 分頁查詢 (SQL 2012+ OFFSET/FETCH 最佳)
    DECLARE @Offset INT = (@PageIndex - 1) * @PageSize;
    SET @SQL = N'SELECT ' + @Fields + N' FROM ' + QUOTENAME(@TableName) +
               CASE WHEN @WhereClause = N'' THEN N'' ELSE N' WHERE ' + @WhereClause END +
               N' ORDER BY ' + @OrderBy + N' OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY';

    SET @Params = N'@Offset INT, @PageSize INT, @TotalRows INT OUTPUT';
    EXEC sp_executesql @SQL, @Params, @Offset, @PageSize, @TotalRows OUTPUT;
END;

常見坑點與避雷

  • 字串型別錯誤:變數宣告用 VARCHAR 而非 NVARCHAR → 報錯。
  • 參數名稱不匹配:@Params 定義 @Id INT 但傳遞 @ID = 1 (大小寫不敏感但名稱必須同)。
  • SQL 注入:表名/欄位名/ORDER BY 子句**不可參數化**,必須用 QUOTENAME 或白名單驗證後拼接。
  • 計畫快取膨脹:高變異度動態 SQL 產生大量計畫,考慮 OPTION (RECOMPILE) 或強制參數化。

結語

sp_executesql 是動態 SQL 的標準入口,核心鐵律:**Unicode 字串、參數化數值、識別碼用 QUOTENAME、複雜邏輯封裝為輔助類**。

copyright © 萬盛學電腦網 all rights reserved