misc.log

日常茶飯事とお仕事と

SQL Server、OpenQueryでのSQLは文字列連結などが使えない?

SQL ServerからOracleにリンクテーブル経由でアクセスしていろいろデータを持ってくる処理を作成中。

サーバー名やらスキーマ名やらをピリオドでつないだSQL記法だとインデックスが使えず遅いため、SQL ServerのOpenQuery命令でSQLごとOracleに渡し、Oracle側で処理させて結果を取る方式を採用します。

SELECT * FROM OPENQUERY(ORACLE01, 'SELECT * FROM TEST WHERE ID=''123''')

こんな感じですね。第2引数のSQLはシングルクォートで囲んだ文字列として渡すので、SQL中の文字列を表すクォートは二重するのも忘れずに、ですね。
で、そこそこのサイズのSQLを投げて無事データが取れました。上記処理自体をストアドプロシージャとして組むこともできました。では、ストアドプロシージャの引数として受け取った条件から作ったWHERE条件を追加しようと、下記のようにやってみました。

DECLARE @CONDITION VARCHAR(2000)
SET @CONDITION = ' AND NAME=''' + @PARAM1 + '''

SELECT * FROM OPENQUERY(ORACLE01, 
             'SELECT * FROM TEST WHERE ID=''123''' + @CONDITION )

すると……「+」のところでエラーが出ます。エラー内容は

'+' 付近に不適切な構文があります。 次が必要です :')'。

なんだこりゃ……。いろいろ試したのですが、ダメでした。SQL自体を変数に放り込んで第2引数を変数だけにしてもダメ。
結局、下記のサイトにある方法、OpenQuery全体を文字列変数に入れてEXECで実行、という方式しかないようです。

リンク サーバー クエリに変数を渡す方法 / KB314520
https://support.microsoft.com/ja-jp/kb/314520

具体的には、こんな感じにします。

DECLARE @OPENQUERY VARCHAR(max)
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(ORACLE01, 
                 ''SELECT * FROM TEST WHERE ID=''''123''''' + @CONDITION + ''')'
EXEC (@OPENQUERY)

一応、スタックオーバーフローでもかかれていましたので転記。

stackoverflow.com