Transact-SQLで動的にSQLを編集してカーソルを使用する

２時間もハマったのでメモ書きとして記述します。

Transact-SQLで動的にSQLを編集してSETでカーソルを定義するとエラーになります。

--動的にSQLを編集する
SET @wkSQL = 'SELECT '
SET @wkSQL = @wkSQL + ' col_name1 AS col_name1 '
SET @wkSQL = @wkSQL + ',col_name2 AS col_name2 '
SET @wkSQL = @wkSQL + 'FROM ' + @table_name  + ' '
SET @wkSQL = @wkSQL + 'WHERE ' + @where_str + ' '
SET @wkSQL = @wkSQL + 'ORDER BY col_name1 '

SET @CrsrVar = Cursor For @wkSQL

の

SET @CrsrVar = Cursor For @wkSQL

でエラーになります。

正しくカーソルで使用できるようにするには以下の通り「EXECUTE」を使用して行います。

DECLARE @wkSQL   [VARCHAR](1024)
DECLARE @col1    [VARCHAR](512)
DECLARE @col2    [VARCHAR](512)

--動的にSQLを編集する
SET @wkSQL = 'SELECT '
SET @wkSQL = @wkSQL + ' col_name1 AS col_name1 '
SET @wkSQL = @wkSQL + ',col_name2 AS col_name2 '
SET @wkSQL = @wkSQL + 'FROM ' + @table_name  + ' '
SET @wkSQL = @wkSQL + 'WHERE ' + @where_str + ' '
SET @wkSQL = @wkSQL + 'ORDER BY col_name1 '

--動的SQLでカーソルオープン
EXECUTE( 'DECLARE CrsrVar CURSOR FOR ' + @wkSQL )

--カーソルオープン
OPEN CrsrVar

--フェッチ開始
FETCH NEXT FROM CrsrVar INTO @col1, @col2

--カーソルクローズ
CLOSE CrsrVar

--リソース開放
DEALLOCATE CrsrVar

これで正しくデータが取れました。