in

TimberLandChapel.com
Community Stage

Untiring efforts and inspiration of the moment...
TimberLandChapel.com supports the engineers who study every day.

ストアドプロシージャについて

Last post 水, 8 13 2008 0:00 by timberlandchapel. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next
  • 火, 8 12 2008 18:52

    • sphere
    • Top 25 Contributor
    • Joined on 火, 8 12 2008
    • Posts 2

    ストアドプロシージャについて

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

    テーブル[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行処理されました)
    ----
    4レコード入っているのですが
    該当レコードは1レコードです。

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

  • 火, 8 12 2008 20:57 In reply to

    Re: ストアドプロシージャについて

    お疲れ様です。 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

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

    いかがでしょう?

    <-------------------------
     TimberLandChapel
     TimberLandChapel.com Community Stage Administrator
    --------------------------->
  • 火, 8 12 2008 22:34 In reply to

    • sphere
    • Top 25 Contributor
    • Joined on 火, 8 12 2008
    • Posts 2

    Re: ストアドプロシージャについて


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

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

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

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

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

  • 水, 8 13 2008 0:00 In reply to

    Re: ストアドプロシージャについて

    お疲れ様です。 TimberLandChapel です。

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

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

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

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

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

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

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

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

    <-------------------------
     TimberLandChapel
     TimberLandChapel.com Community Stage Administrator
    --------------------------->
Page 1 of 1 (4 items)
©TimberLandChapel.com all right reserved 2005-2011.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems