Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2001
    Location
    Canada
    Posts
    45

    Unhappy Unanswered: Problem with 'IN'

    I want to create a store procedure like this

    CREATE PROCEDURE test
    @values VARCHAR(8000)
    AS

    SELECT *
    FROM MyTable
    WHERE MyTable.ID IN(@values)
    GO

    the parameter @values = '''1'',''3'',''5'',''6'',''7'''

    How can I do that??


    Thanks

    Franky
    franky@boucheros.com
    Franky
    FBoucheros@hotmail.com

  2. #2
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72

    Re: Problem with 'IN'

    Try writing the SELECT sentence an a string and then EXECUTE this string.

    Originally posted by Franky
    I want to create a store procedure like this

    CREATE PROCEDURE test
    @values VARCHAR(8000)
    AS

    SELECT *
    FROM MyTable
    WHERE MyTable.ID IN(@values)
    GO

    the parameter @values = '''1'',''3'',''5'',''6'',''7'''

    How can I do that??


    Thanks

    Franky
    franky@boucheros.com

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Problem with 'IN'

    CREATE PROCEDURE test (@values VARCHAR(8000))
    as

    Declare @Query nVarchar(1000)

    SET @Query=N'SELECT * FROM Table ' +
    'WHERE Table.ID IN( ' + @values + ')'


    EXECUTE sp_executesql @Query, N'@level tinyint', @level = 35


    ====================================

    Where @values must be

    @values = '''1''' + ','+ '''3'''+ ',' + '''5''' + ','+'''6'''+','+'''7'''

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Lots of ways to skin this cat. A non-dynamic solution:

    SELECT *
    FROM MyTable
    WHERE @values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

    Another method would be to create a user-defined funtion that returns a table of values from your string.

    blindman

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by blindman
    Lots of ways to skin this cat. A non-dynamic solution:

    SELECT *
    FROM MyTable
    WHERE @values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

    Another method would be to create a user-defined funtion that returns a table of values from your string.

    blindman
    Or this way

    SELECT *
    FROM MyTable
    WHERE CHARINDEX(@values,cast(MyTable.ID as varchar))>0

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

    blindman

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by blindman
    Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

    blindman
    blindman - it was my fault but another one - it needs to change order of arguments in charindex function. Nothing is wrong with quotes. Check this one:

    create table #test(id int identity,code varchar(10))
    insert #test(code) values('a')
    insert #test(code) values('b')
    insert #test(code) values('c')

    declare @list varchar(80)
    set @list='''1'',''3'',''5'',''6'',''7'''
    select * from #test
    where CHARINDEX(cast(id as varchar),@list)>0

Posting Permissions

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