SQL Server: Dynamic query with IN clause


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: SQL Server: Dynamic query with IN clause

  1. #1
    Join Date
    Dec 2005
    Location
    South of Nowhere, Maine
    Posts
    309

    SQL Server: Dynamic query with IN clause

    I have a query (several of them) that need to use the IN clause. This IN clause is somewhat dynamic. I have two possible combinations.

    SELECT * FROM myTable WHERE myField IN ('A','B','C')

    or

    SELECT * FROM myTable WHERE myField IN ('X','Y')

    I would like to use stored procedures, but I don't want to use the EXEC @sql function if I can help it. These queries are executed over and over again, so having a execute plan would help speed things up.

    The other idea, is to just have two stored procs. Identical except for one always uses the 'X','Y' for the IN, and the other uses the 'A','B','C' approach.

    Any suggestions?

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Eric: It is not possible to use a parameter as input to the IN clause. I would probably write a function to parse a comma-delimited parameter and insert the individual items into a temp table, then use a JOIN on that table instead of an IN clause. You might also consider passing the list as XML and using OPENXML to shred the XML into a temp table.

    Or, you could just create two stored procs. ;-)
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Dec 2005
    Location
    South of Nowhere, Maine
    Posts
    309
    Ok. For now, I'll use the dynamic queries (sql in the code). I'll try the other options and see what works best for each case. I have several places to make the distinction.

    Thanx!

  4. #4
    Join Date
    Feb 2006
    Posts
    55
    Some basic examples. I don't think you'll have a problem with the caching on the execution plan if you opt for dynamic sql on SQL Server 2000. Query plans for ad-hoc queries are automatically parameterized and cached.
    I prefer to avoid dynamic SQL unless its absolutely necessary. You need to be aware that the dynamic SQL executes as the user you might need to grant permissions to underlying tables. You should also provide additional validation of user input to prevent SQL injection attacks and user error.



    -- Example1 --
    exec pInTest 1
    exec pInTest 2
    -- Example2 --
    exec pInTest2 1
    exec pInTest2 2
    -- Example3 --
    exec pInTest3 '''A'',''B'',''C'''
    exec pInTest3 '''X'',''Y'''

    -- Example1 --
    create proc pInTest(@In tinyint)
    as
    if @In = 1
    SELECT * FROM myTable WHERE myField IN ('A','B','C')
    if @In=2
    SELECT * FROM myTable WHERE myField IN ('X','Y')

    -- Example2 --
    create proc pInTest2(@In tinyint)
    as
    SELECT * FROM myTable WHERE (
    (myField IN ('A','B','C') and @In=1)
    or (myField IN ('X','Y') and @In=2))

    -- Example3 --
    create proc pInTest3(@In varchar(50))
    as
    declare @query nvarchar(4000)
    set @query = 'SELECT * FROM myTable WHERE myField IN(' + @In + ')'
    exec sp_executesql @query
    David Wiseman
    MCSE (2000/2003), MCSA (2003), MCDBA
    www.wisesoft.co.uk


Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 05:14 PM
  2. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 01:22 PM
  3. Replies: 0
    Last Post: 05-16-2001, 01:53 PM
  4. Can Sql Server do this?
    By DavidR in forum Database
    Replies: 5
    Last Post: 02-15-2001, 12:46 AM
  5. SQL server administrative knowledge
    By Becky in forum Database
    Replies: 1
    Last Post: 07-11-2000, 04:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center