none
ストアドプロシージャでのIN句 RRS feed

  • 質問

  • お世話になります。

    VS2010よりストアドを使い結果を使用しています。

    ストアドに文字列のパラメータを渡して、ストアド内でWHERE条件にIN句で使用しておりますが
    想定している結果が返ってきません。

    @WORK  NVARCHAR  (50)

    SELECT AAA,BBB FROM CCC WHERE AAA IN (@WORK)
    のように使用しております。

    AAAは文字列で、@WORK内には、'ABC','DEF','GHI'といった感じになっております。
    @WORKに入れるパラメータは可変です。

    どのように記述すればいいのでしょうか。。。

    2010年10月1日 10:09

回答

  • Visual Studio 2010と示しておきながらSQL Server 2005はまったくのトラップですね。そもそもVisual Studioはこの場合エディタでしかなくバージョンなんかどうでもいいのに。

    M_Lewisさんの回答で動作しますが、SQLインジェクションに遭う可能性があるため、気を付けてください。

    ストアドプロシージャ側で@WORKを正しくエスケープできればそれに越したことがないのですが、SQLでの文字列処理はとても面倒でして…。

    2010年10月4日 0:58
  • SELECT AAA,BBB FROM CCC WHERE AAA IN (@WORK)

    というSQLではIN句内のパラメータは一つしかないと言っていることになります。@WORKに'1, 3, 5'を渡しても、'1, 3, 5'という一つの文字列であるAAAを探すことになります。AAAが1または3または5であるという解釈にはなりません。
    複数のパラメータを渡す場合は、

    SELECT AAA,BBB FROM CCC WHERE AAA IN (@WORK1, @WORK2, @WORK3)

    などと書かなければなりません。言うまでもなく、IN句内に3つのキーワードがあることになります。したがって、IN句内のキーワードの個数が動的に変化する場合は、上のような形でSQLを書くことができません。そこで、文字列を組み立てることになります。文字列を組み立てますので、佐祐理さんも言われていますがSQLインジェクションが起こらないように対策を行って下さい。SQLインジェクション対策は非常に大切なことですので、しっかりと理解されることをお勧めします。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2010年10月4日 1:32

すべての返信

  • USE AdventureWorks
    GO
    
    DECLARE @a AS int
    DECLARE @b AS int
    DECLARE @c AS int
    SET @a = 1
    SET @b = 3
    SET @c = 5
    SELECT * FROM Person.Contact WHERE ContactID IN (@a, @b, @c)
    GO
    
    DECLARE @d AS nvarchar(50)
    DECLARE @e AS nvarchar(255)
    SET @d = '1, 3, 5'
    SET @e = 'SELECT * FROM Person.Contact WHERE ContactID IN (' + @d + ')'
    EXEC sp_executesql @e
    GO
    

    Please mark the thread as Answered when an answer resolves your problem. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう
    2010年10月1日 15:22
  • SQL Serverのストアドプロシージャについての質問なのに、SQL Serverのバージョンが書かれていませんが…。
    SQl Server 2008以降ならテーブル値パラメーター を使って文字列を渡すことができます。

    -- 事前にユーザー定義テーブル型を作成しておく
    CREATE TYPE StringType AS TABLE ( NAME nvarchar(50) );
    GO

    -- ストアドプロシージャ本体
    CREATE PROCEDURE SAMPLE @WORK StringType READONLY
    AS
      SELECT AAA, BBB FROM CCC WHERE AAA IN (SELECT NAME FROM @WORK);
    GO

    こんな感じかな。

    2010年10月1日 17:07
  • Visual Studio のバージョンではなく SQL Server のバージョンが知りたいところですが、もし SQL Server 2008 以降なら、ストアドプロシージャの引数に、テーブル値パラメータを渡して複数値を渡すようにしてはどうでしょうか。

    http://msdn.microsoft.com/ja-jp/library/bb510489.aspx

    2010年10月1日 17:12
  • みなさん回答ありがとうございました。

    SQL Serverのバージョンですが、2005です。すいません書き忘れておりました。

    そうなれば、2008から有効なテーブル値パラメータは使用できないのですよね。。。;。;

    すいませんが、無知な私によきアドバイスお願いします。。。

    2010年10月3日 23:51
  • M_Lewisさんの回答でSQL Server 2005でも大丈夫ですよ。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2010年10月4日 0:06
  • Visual Studio 2010と示しておきながらSQL Server 2005はまったくのトラップですね。そもそもVisual Studioはこの場合エディタでしかなくバージョンなんかどうでもいいのに。

    M_Lewisさんの回答で動作しますが、SQLインジェクションに遭う可能性があるため、気を付けてください。

    ストアドプロシージャ側で@WORKを正しくエスケープできればそれに越したことがないのですが、SQLでの文字列処理はとても面倒でして…。

    2010年10月4日 0:58
  • trapemiyaさんありがとうございます。

    度々すいません。もう少しお付き合いください。

    >DECLARE @d AS nvarchar(50)
    >DECLARE @e AS nvarchar(255)
    >SET @d = '1, 3, 5'
    >SET @e = 'SELECT * FROM Person.Contact WHERE ContactID IN (' + @d + ')'
    >EXEC sp_executesql @e
    >GO

    今回私の質問は、VS2010からパラメータを不特定多数渡すようにしております。

    ストアド内の
    @WORK  NVARCHAR  (50)

    SELECT AAA,BBB FROM CCC WHERE AAA IN (@WORK)

    @WORK箇所に直接、文字を記述したら問題なく結果が返ってくるのですが。。。

    すいませんがよきアドバイスお願いします。。^^;

    2010年10月4日 1:00
  • SELECT AAA,BBB FROM CCC WHERE AAA IN (@WORK)

    というSQLではIN句内のパラメータは一つしかないと言っていることになります。@WORKに'1, 3, 5'を渡しても、'1, 3, 5'という一つの文字列であるAAAを探すことになります。AAAが1または3または5であるという解釈にはなりません。
    複数のパラメータを渡す場合は、

    SELECT AAA,BBB FROM CCC WHERE AAA IN (@WORK1, @WORK2, @WORK3)

    などと書かなければなりません。言うまでもなく、IN句内に3つのキーワードがあることになります。したがって、IN句内のキーワードの個数が動的に変化する場合は、上のような形でSQLを書くことができません。そこで、文字列を組み立てることになります。文字列を組み立てますので、佐祐理さんも言われていますがSQLインジェクションが起こらないように対策を行って下さい。SQLインジェクション対策は非常に大切なことですので、しっかりと理解されることをお勧めします。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2010年10月4日 1:32
  • trapemiyaさん、佐祐理さん

    度々のご指摘ありがとうございます。

    おっしゃる通りにSQLインジェクションに気を付けながらやってみます。

    長い間お付き合いありがとうございました。。

    またよろしくお願いします。

    2010年10月4日 4:57