ITコーディネータのシュウです。

会社の人が広島に行ったときに、夕方近くに平和公園に散歩がてら立ち寄って撮った、原爆ドームの写真です。昔、小学校の修学旅行で広島の平和公園に行ったことがあり、千羽鶴がたくさんつるしてあったのを見た記憶がありますね。子供心に、戦争や原子爆弾の被害が二度とあってはならないと強く感じました。

＜本日の題材＞
動的SQL(SQL Server)

今回は、SQL Serverの場合の動的SQLの続きで、sp_executesql システムストアドプロシージャを利用した例です。

構文
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]

@stmtにはパラメータを含めることができ、パラメータ定義リスト(@params=..)とパラメータ値リスト(@param1=..)の両方に指定する必要があります。

例）受注テーブルから、指定した受注日と決済金額の条件に合った受注データを抽出する例で、条件句(WHERE句)にパラメータ変数を用いています。

DECLARE @sql_stmt nvarchar(500)
DECLARE @tab_Name nvarchar(30) = 'dbo.受注テーブル'
DECLARE @param nvarchar(200)
SET @sql_stmt = N'SELECT * FROM ' + @tab_Name
SET @sql_stmt = @sql_stmt + N' WHERE 受注日= @p1_date'
SET @sql_stmt = @sql_stmt + N' AND 決済金額>= @p2_kingaku'
SET @param = N'@p1_date varchar(8), @p2_kingaku int'

EXEC sp_executesql@sql_stmt
, @param
, @p1_date = '20141029'
, @p2_kingaku = 300000

結果は、以下

条件として、受注日(@p1_date)に「20141029」、決済金額(@p2_kingaku)に「300000」以上をパラメータとして与えた場合の、受注テーブルのデータが抽出されました。

※）注意点として、最終的に変数@sql_stmtに設定したSQL文が正しいSQL文になるように、FROM句やWHERE句、AND句などの前後に空白をきちんと入れておく必要があります。（前回投稿の例でもこの点は同じですが）
もし、上記の例でWHEREの前の空白を入れていないと、エラーになります。上記をストアドプロシージャにした場合は、プロシージャのコンパイルはできるけれども、実行時にエラーになってしまうということが発生します。

また、パラメータを繰り返し変更しながらデータを取得するようなケースで、sp_executesql を利用することでかなりレスポンスを改善できる場合がありますが、それについては次回以降にまた機会を改めて、題材として取り上げようと思います。

今日は以上まで

ITコーディネータのシュウです。

コスモスがいっぱい咲いてました。ちょっと前の台風で、結構倒れてしまっていたのですが、何とか復活して頑張って咲いています。

＜本日の題材＞
動的SQL(SQL Server)

今回は、SQL Serverの場合の動的SQLについての内容です。SQL Serverの場合は、EXECUTEステートメント、もしくは、sp_executesql システムストアドプロシージャを利用します。

例）テーブル名や項目名を変数化して、指定したテーブルから必要な項目についてのデータを抽出する動的SQLの例です。EXECUTEステートメントを使用した場合です。

受注テーブルのデータを指定した条件で抽出します。
その前に、データを確認
SELECT * FROM dbo.受注テーブル
ORDER BY 受注NO;

受注テーブルの定義は、Oracleのときと同じ

SQLは以下：

DECLARE @tab_name VARCHAR(30)
DECLARE @Col_name VARCHAR(100)
DECLARE @Clause VARCHAR(200)
SELECT @tab_name = 'dbo.受注テーブル'
SELECT @Col_name = '顧客番号, 受注日, 決済金額'
SELECT @Clause = '受注日 BETWEEN ''20141028'' AND ''20141029'''

EXECUTE ('SELECT '+@Col_name+' FROM '+ @tab_name+' WHERE '+ @Clause)

パラメータとして、@tab_name にテーブル名、@Col_nameにカラム名、@Clauseには条件を渡し、それを実行します。
（SQL Serverでも、'で囲む文字列の中に'（シングルクォーテーション）を含む場合には、'を2つ連続して記述します）
また、EXECUTEは「EXEC」と省略することもできます。

結果は、以下

受注日が20141028～20141029の期間内での顧客番号、受注日、決済金額を抽出しています。
この各パラメータを変更すれば、動的に対象のテーブル、項目、条件を設定できます。以下は変更した場合の例。

DECLARE @tab_name VARCHAR(30)
DECLARE @Col_name VARCHAR(100)
DECLARE @Clause VARCHAR(200)
DECLARE @sql_stmt VARCHAR(400)
SELECT @tab_name = 'dbo.受注テーブル'
SELECT @Col_name = '顧客番号, 決済金額'
SELECT @Clause = '決済金額 > 300000'
SELECT @sql_stmt = 'SELECT '+@Col_name+' FROM '+ @tab_name+' WHERE '+ @Clause

EXEC (@sql_stmt)

結果は、以下

ここでは、最終的に @sql_stmt に組み合わせたSELECT文セットして、それを実行しています。決済金額が 300000円以上の顧客について、顧客番号と決済金額を表示できました。

今日は以上まで