在 SQL Server 開發中,常需根據執行時期才能確定的 SELECT 陳述式建立游標。由於 T-SQL 靜態游標要求編譯時期即知晓查詢語法,動態游標成為解決「查詢語句由參數傳入」問題的關鍵技術。
本方案採用「臨時表中轉」策略:
SELECT 陳述式,提取欄位清單與資料表清單INSERT INTO #TempTable EXEC(@DynamicSQL) 將查詢結果填入臨時表CREATE PROCEDURE Usp_CreateCursor
@Select_Command VARCHAR(8000),
@Cursor_Return CURSOR VARYING OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Select_Command_Temp VARCHAR(8000),
@Table_List VARCHAR(255),
@Column_List VARCHAR(8000),
@From_Pos INT, @Where_Pos INT, @Having_Pos INT,
@Groupby_Pos INT, @Orderby_Pos INT, @Temp_Pos INT;
/* 1. 正規化 SELECT 陳述式 */
IF LEFT(LOWER(LTRIM(@Select_Command)), 6) <> 'select'
SET @Select_Command = 'SELECT ' + @Select_Command;
SET @Select_Command_Temp = LOWER(LTRIM(@Select_Command));
IF LEFT(@Select_Command_Temp, 6) = 'select'
SET @Select_Command_Temp = RIGHT(@Select_Command_Temp, LEN(@Select_Command_Temp) - 7);
/* 2. 定位關鍵字位置,切割 TABLE 與 WHERE 子句 */
SELECT @From_Pos = CHARINDEX(' from ', @Select_Command_Temp),
@Where_Pos = CHARINDEX(' where ', @Select_Command_Temp),
@Having_Pos = CHARINDEX(' having ', @Select_Command_Temp),
@Groupby_Pos = CHARINDEX(' group by ', @Select_Command_Temp),
@Orderby_Pos = CHARINDEX(' order by ', @Select_Command_Temp);
SET @Temp_Pos = CASE
WHEN @Where_Pos > 0 THEN @Where_Pos
WHEN @Having_Pos > 0 AND (@Temp_Pos = 0 OR @Having_Pos < @Temp_Pos) THEN @Having_Pos
WHEN @Groupby_Pos > 0 AND (@Temp_Pos = 0 OR @Groupby_Pos < @Temp_Pos) THEN @Groupby_Pos
WHEN @Orderby_Pos > 0 AND (@Temp_Pos = 0 OR @Orderby_Pos < @Temp_Pos) THEN @Orderby_Pos
ELSE 0 END;
IF @Temp_Pos > 0
SET @Table_List = SUBSTRING(@Select_Command_Temp, @From_Pos + 6, @Temp_Pos - @From_Pos - 6);
ELSE
SET @Table_List = SUBSTRING(@Select_Command_Temp, @From_Pos + 6, LEN(@Select_Command_Temp) - @From_Pos - 5);
/* 3. 解析欄位清單,處理 * 與 別名 */
SET @Select_Command_Temp = LEFT(@Select_Command_Temp, @From_Pos - 1);
WHILE LEN(@Select_Command_Temp) > 0
BEGIN
/* 以逗號分隔欄位,查詢 sys.columns 取得型別資訊 */
/* ... 此處省略詳細解析迴圈,核心邏輯為:
- 分割欄位名稱
- 識別 TABLE.COLUMN 或 * 格式
- 透過 sys.columns / sys.types 取得資料型別、長度、精度
- 組裝 CREATE TABLE #Temp 所需欄位定義語法 */
END
/* 4. 動態建立臨時表並填入資料 */
DECLARE @CreateTempSQL NVARCHAR(MAX) = 'CREATE TABLE #DynamicCursorTemp (' + @Column_List + ')';
EXEC sp_executesql @CreateTempSQL;
DECLARE @InsertSQL NVARCHAR(MAX) = 'INSERT INTO #DynamicCursorTemp ' + @Select_Command;
EXEC sp_executesql @InsertSQL;
/* 5. 宣告並輸出游標 */
SET @Cursor_Return = CURSOR FORWARD_ONLY STATIC FOR
SELECT * FROM #DynamicCursorTemp;
OPEN @Cursor_Return;
END;
INSERT INTO #Temp EXEC(@SQL) 是將動態查詢結果物質化的核心技巧,繞過了「無法直接對字串變數宣告游標」的限制。
利用 sys.columns、sys.types 取得欄位完整定義,確保臨時表結構與查詢結果完全對應,避免型別不符錯誤。
FORWARD_ONLY STATIC 組合提供:唯讀、僅向前捲動、結果集在開啟時固定,效能最佳且避免並發衝突。
DECLARE @MyCursor CURSOR;
EXEC Usp_CreateCursor
'SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate > ''2023-01-01''',
@MyCursor OUTPUT;
FETCH NEXT FROM @MyCursor; -- 正常使用游標
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
OPENJSON / STRING_SPLIT 簡化解析邏輯