using IN operator in stored procedure

  • Hi

    I'm having a problem using the IN operator within a stored procedure (I'm assuming that you can). I'm working with a list box on an access front-end, and if multiple records are selected from the list then I get a string something like ('99', '100', '101') where each number represents attribute ID (PK) from tbl.  I then want to run the sp below inserting the string after the IN operator; exec spGoToMultiRec STRINGHERE.  I've tried declaring variable idList as int, char, varchar, etc but keep getting errors.

    CREATE PROCEDURE dbo.spGoToMultiRec @idList int

    AS

    SELECT  *

    FROM   tbl

    WHERE  ID IN  (@idList)

    GO

    Any help would be greatly appreciated.

    Thanks

    Brian

  • Been there done that.  Unfortunately, it does not work as easy as doing an IN if you are not using dynamic sql.

    I appologize to the creator of this method, I have forgot his name from these forums, but here was a suggestion that I have previously gotten.

    declare @t table (

     i int

    )

    insert into @t (i) values (1)

    insert into @t (i) values (2)

    insert into @t (i) values (3)

    insert into @t (i) values (4)

    insert into @t (i) values (5)

    declare @j-2 varchar(10)

    set @j-2 = '1,3,5,'

    select * from @t WHERE CharIndex(',' + cast(i as varchar) + ',' , ',' + @j-2) > 0

  • Brian, I replied to your other post but will reply here also.

    The IN clause will not take a comma delimited list even though that is the way it looks when you type in a list manually.

    There are a couple of ways I have dealt with this but for your purposes you would probably want to use a function that returns a Table type variable.

    First of all you would pass your list in as a string (varchar). Then create a function as follows to "split" the id's into actual rows in a table variable and then select all the rows as your function return value:

    CREATE FUNCTION Split

     (@List  varchar(1000))

    RETURNS @Results table

     (Item varchar(1000))

    AS

    begin

     declare @IndexStart int

     declare @IndexEnd int

     declare @Length  int

     declare @Delim  char(1)

     declare @Word  varchar(1000)

     set @IndexStart = 1 

     set @IndexEnd = 0

     set @Length = len(@List)

     set @Delim = ','

     

     while @IndexStart <= @Length

          begin

      

      set @IndexEnd = charindex(@Delim, @List, @IndexStart)

      

      if @IndexEnd = 0

       set @IndexEnd = @Length + 1

      

      set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)

      

      set @IndexStart = @IndexEnd + 1

      

      INSERT INTO @Results

       SELECT @Word

          end

     

     return

    end

    Then rewrite your IN clause as follows:

    CREATE PROCEDURE dbo.spGoToMultiRec @idList varchar(1000)

    AS

    SELECT  *

    FROM   tbl

    WHERE  ID IN (SELECT * FROM dbo.Split(@idList))

    GO

    You'll probably have to tweak the function to return the proper data type.

    Hope this helps.

  • Similar to the above solution, you could create a temp table in a business object, populate it with the entries selected, and write the stored procedure to use the temp table.  If it's common I might be inclined to use the function, but the way I've described above works very well for us.  It's even fast.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply