TimberLandChapel.com Community Stage

こんにちは。
いろいろなサイトを探してみたですが
答えが出ず質問させて下さい。

テーブル[a_test]
PN_ID   int
PN      varchar(20)
QUANT   int
いずれのフィールドもNULL不許可を設定しています。

この上で以下のストアドを作成しました。
----
CREATE PROCEDURE [dbo].[sp_quantmodify]
(
@paramPN     varchar(20),
@paramQUANT  int
)
AS
DECLARE @strSQL  nvarchar(1000)

SELECT * FROM dbo.a_test
SET @strSQL = N'UPDATE dbo.a_test '
            + N'SET QUANT=@paramQUANT '
            + N'WHERE PN=@paramPN;'
EXEC(@strSQL)
SELECT * FROM dbo.a_test
----
これをManagement Studio上で実行すると
パラメータを聞いてくるのでそれぞれの値を入れて
実行すると

----
(4行処理されました)
メッセージ137　レベル15　状態2　行1
スカラ変数　"@paramQUANT"を宣言してください。

(4行処理されました)
----
４レコード入っているのですが
該当レコードは１レコードです。

宣言の仕方がおかしいのでしょうか？
よろしくお願いいたします。

お疲れ様です。 TimberLandChapel です。

宣言の方法ではなく，ストアドプロシージャ内での変数の利用方法を誤解されているようです。

確かに，より複雑なクエリを動的に編成するためには，

　@query nvarchar や @strSQL varchar などの変数に構築済みのクエリを格納してEXECする

という今回使われた方法をとることはあります。

しかし，この方法は，選択してくる列を動的に構成するとか，対象テーブルを切り替えるなどのより複雑な案件の場合です。

WHERE 句に使うような一般的なパラメータは，ストアドの入力パラメータとして宣言した時点で，内部で利用できるものになります。

具体的には，

CREATE PROCEDURE [dbo].[sp_quantmodify]
(
@paramPN     varchar(20),
@paramQUANT  int
)
AS
DECLARE @strSQL  nvarchar(1000)

SELECT * FROM dbo.a_test

UPDATE dbo.a_test
SET QUANT=@paramQUANT
WHERE PN=@paramPN ;
 
SELECT * FROM dbo.a_test

GO

とするだけで，おそらく意図されていることはできます。（実際の要件はわかりませんが）

いかがでしょう？

ご回答有難うございます。

ご提示いただいたコードに変更しましたら動作しました。
有難うございます。

>宣言の方法ではなく，ストアドプロシージャ内での変数の利用方法を誤解されているようです。
>確かに，より複雑なクエリを動的に編成するためには，
>@query nvarchar や @strSQL varchar などの変数に構築済みのクエリを格納してEXECする
>という今回使われた方法をとることはあります。

そうですね…確かに今回は変数を宣言してＳＱＬを保存しなくとも実行は出来るのですが、
ここからプロシージャの機能を拡張しようとして宣言をしたのが間違いの元でした。
ところで、最初の思惑通りＳＱＬ文に投げてそれをEXECしたい場合、最初のコードをどの様に変更すれば
良いのでしょうか？
お答えを頂いたのに質問を繰り返すようで申し訳ありませんがよろしくお願い申し上げます。

今回のＳＱＬサーバーの勉強にあたりバナーにある本を拝読させて頂きました。
私にも大変解かりやすく導入が出来ました。有難うございます。

お疲れ様です。　TimberLandChapel です。

SQL文を変数内に組み立てる方法は，

• セキュリティ
• パフォーマンス

の観点からよっぽどの理由がない限りお勧めできませんが，

SET @query = 'UPDATE - - - SET PN_int =' + CAST(@int AS varchar) + ' WHERE PN=' + @varchar + ';'

というように直接文字列としてのクエリを完成していくようになります。 

どのような拡張をするかによりますが，

ストアドプロシージャをいくつかに分割して階層コールするなど，他の方法で対応されたほうがよいと思います。

あとは，nvarchar と varchar が混じっているのもあまり望ましくないですね。