SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: SQL IN operator

  1. #1
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL IN operator

    I want to pass a parameter a string link this:

    '3,4,5'

    I want to then use the IN operator to search for any of these number in an ID field which is an INT.

    Code:
    @idsString nvarchar(MAX)
    
    SELECT	*
    FROM	MyTable
    WHERE
    MyTable.IntID IN (idsString)
    when I pass the string I get the error:

    Msg 245, Level 16, State 1, Procedure pMyProc, Line 30
    Conversion failed when converting the nvarchar value '3,4,5' to data type int.

    The second issue is that this string may also be empty so I want to do :

    Code:
    @idsString nvarchar(MAX)
    
    SELECT	*
    FROM	MyTable
    WHERE
    MyTable.IntID IN ISNULL((idsString), MyTable.IntID)
    but this won't even compile.

    Any help/advice?

    cheers
    monkey - the rest is history

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you need to use dynamic sql

    construct the sql string like this (caution: this is pseudocode) --
    Code:
    @sql = 'SELECT * FROM MyTable WHERE MyTable.IntID IN ( '
    @sql = @sql + @idsString 
    @sql = @sql + ' )'
    
    EXEC ( @sql )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've done the following in the past without Dynamic SQL by implementing a SPLIT UDF and then doing something like:
    Code:
    SELECT *
    FROM whatever
    WHERE id IN SPLIT(@idsString);
    In my opinion, it's better than Rudy's suggestion, because it's not interpreted. That makes it more secure (no need to worry about SQL injection at the DB layer), better performing because the query is compiled on creation of the procedure (so utilizes MS-SQL's full procedure optimization), easier to debug, and easier to maintain long term.

    Of course, Rudy's example doesn't require the creation of a UDF first...

    Cheers.

  4. #4
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    I've done the following in the past without Dynamic SQL by implementing a SPLIT UDF and then doing something like:
    Code:
    SELECT *
    FROM whatever
    WHERE id IN SPLIT(@idsString);
    In my opinion, it's better than Rudy's suggestion, because it's not interpreted. That makes it more secure (no need to worry about SQL injection at the DB layer), better performing because the query is compiled on creation of the procedure (so utilizes MS-SQL's full procedure optimization), easier to debug, and easier to maintain long term.

    Of course, Rudy's example doesn't require the creation of a UDF first...

    Cheers.
    this seems lke the way to go for me

    Right i have:

    Code:
    SELECT	*
    FROM	MyTable
    WHERE	MyTable.intID IN (Select(dbo.SPLIT(@idsString)))
    but i get this error:
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SPLIT", or the name is ambiguous.
    monkey - the rest is history

  5. #5
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't remember the exact implementation... as far as I recall, SPLIT returns a resultset, so you should be able to do something like "SELECT * FROM dbo.SPLIT(@idsString);"

    Try to get that working by itself first and then worry about putting it in an "IN" statement.

  6. #6
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    I don't remember the exact implementation... as far as I recall, SPLIT returns a resultset, so you should be able to do something like "SELECT * FROM dbo.SPLIT(@idsString);"

    Try to get that working by itself first and then worry about putting it in an "IN" statement.
    ok - so this work fine:

    Code:
    declare @idsString nvarchar(max)
    set @idsString = N'2,3,4'
    SELECT * FROM dbo.SPLIT(@idsString,1,0)
    returning
    2
    3
    4

    but this still fails:

    Code:
    declare @idsString nvarchar(max)
    set @idsString = N'2,3,4'
    SELECT	*
    FROM	mytable
    WHERE	mytable.intID IN (dbo.SPLIT(@idsString,1,0))
    monkey - the rest is history

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    SELECT *
    FROM mytable
    WHERE mytable.intID IN (SELECT * FROM dbo.SPLIT(@idsString,1,0))

  8. #8
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    Try this:

    SELECT *
    FROM mytable
    WHERE mytable.intID IN (SELECT * FROM dbo.SPLIT(@idsString,1,0))
    Top job! realised my initial mistake now! missed the * FROM !!!

    Thanks for all your help - great function (and you Rudy )!

    monkey
    monkey - the rest is history

  9. #9
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •