QA@IT

SQL Serverのプロシージャにて動的なSQLを実行し、実行結果をCursorとしてOutputする。

21254 PV

初めまして、業務でasp.netのページ作成をおこなっております。

aspで入力した条件で検索を行い検索結果をページ上に出力するページを作成しているのですが、現行のasp上でSQL(Select文)を構築・実行する仕様ではタイムアウトを頻発してしまい、asp上で入力されたデータを引数に動的にSQLを構築し、検索結果をCursorの戻り値として返すという仕様に変更するための開発を行なっております。

これまでの調査で

  • 動的なSelect文を実行し、1件のデータを取得する
  • 動的なSelect文を実行し、Cursorを取得しプロシージャ内でデータの参照
  • 静的なSelect文を実行し、検索結果をCursorの戻り値として返す

という行程については理解したのですが

  • 動的なSelect文を実行し、検索結果をCursorの戻り値として返す

を実行するための方法がわかりません。

よろしければ知恵を貸していただけないでしょうか? よろしくお願いします。

仕様

OS:Windows 2003 Server Standard
DB:SQLServer2005SP2 Standard
asp.net:C#で構築

ソース(一部簡略、本番ではSQLのWhere句を動的に構築)

ALTER PROCEDURE [dbo].[GetData](
    @GetCur CURSOR VARYING OUTPUT       -- 出力データ
)
AS
BEGIN
    -- 変数設定
    DECLARE @SQL_Select         nvarchar(4000)

    -- SQL構築
    SET @SQL_Select = 'DECLARE CUR_GET CURSOR FOR '
    SET @SQL_Select = @SQL_Select +  'SELECT  col_1'
    SET @SQL_Select = @SQL_Select + '     , col_2'
    SET @SQL_Select = @SQL_Select + '     , col_3'
    SET @SQL_Select = @SQL_Select + '  FROM Test_Table '

    -- SQL実行
    EXEC sp_executesql @SQL_Select

    -- 実行結果をCursor @GetCurに設定
    -- ※この部分についてわかりません。

    DEALLOCATE CUR_GET
END

すいません。よろしくお願いします。

  • テーブル変数や一時テーブルで返すのではダメですか? -
  • 返信が遅れてすいません。 一時テーブルを定義し、Cursorとして検索結果を取得していた動的SQLを一時テーブルにInsertするという形式に変えたところうまくいきました。 
    ありがとうございます。
    -

回答

そもそもカーソルが必要なのでしょうか?
SQL ServerはSELECTした結果をそのままアプリケーションに返すことができるので、カーソルってストアド内での処理以外にあまり使わない気がするのですが。。。

環境がない&記憶ベースなので直接的な回答が難しいのですが、以下のサイトなどを参照してみてはどうでしょうか。
SQL Serverのストアドで動的SQLを使用するやり方が書かれています。
http://d.hatena.ne.jp/rezev_hikaru/20100329/1269832705

当然ながら動的SQLはSQLインジェクションの脆弱性を持つ可能性が高くなるので、可能な限りPrepared Statementで対応できないか検討してください。
sp_executesqlを使用する場合であっても、パラメータ化可能な部分はパラメータとして渡すようにしてください。

編集 履歴 (0)

EXEC sp_executesql @SQL_Select
の次に、
OPEN CUR_GET
SET @GetCur = CUR_GET
ではどうですか?

編集 履歴 (0)
  • 解答ありがとうございます。
    実際に上記で試してみたのですが

    メッセージ 206、レベル 16、状態 2、プロシージャ GetData、行 0
    オペランド型の不整合: int は cursor と互換性がありません

    のエラーが吐き出されました。
    -
  • あれ?そうですか。こちらで試したらエラーでなかったのですが、不思議ですね。実際のSQLにはint型の変数があるのですか? -

読み返してみると、的外れな回答でしたね。申し訳ございませんでした。

編集 履歴 (0)

こんにちは。

確認していないですが、このサイトのやり方でどうですか?
http://bokuibi.blogspot.jp/2009/05/sql-serversql.html

編集 履歴 (0)
  • 解答ありがとうございます。

    URLにあるように”動的なSelect文を実行しCursorに取得”については可能なのですが
    現在困っているのは”取得したCursorを戻し値として返す、もしくはOutput用カーソルへデータを挿入する"
    という点になります。

    こちらの説明が不足していた点があり申し訳ありません。
    この点でわかることがあると大変助かります。
    -
ウォッチ

この質問への回答やコメントをメールでお知らせします。