Using a parameter for the "IN" clause

All,

Having some trouble with getting the "IN" statement to
return results from a stored procedure. I want to pass in
a string of values, such as ('999999', '111111'), to my
stored procedure and use it in a select statement.

The SP looks something like this...

@someNumbers [varchar](1200)

SELECT * FROM someTable WHERE someNumber IN (@someNumbers)

This doesn't return any data. How do I define something
like this in the stored proc

Thanks
Mike

Answer this question

Using a parameter for the "IN" clause

Venkat Sathyamurthy
Only way i can think of at the moment is by using the CharIndex and Substring functions to break the passed in string down, and possibly use a Table variable to store the values.

Then you could construct a dynamic where clause from the values in the Table variable
kaushalparik
the stored procedure/dynamic sql approach will work. . .

just keep in mind, if you use that approach in an ado command/.net command to perform an update/delete/insert, I dont think the recordsaffected value of the ADODB.command.execute method, or the return of the XXXCommand.ExecuteNonQuery method will reflect the true number of records that were affected.

If I remember correctly, when I used a dynamic SQL script as the commandtext for an ADODB.Command object, recordsaffected returned -1
Rebecca Karma

right heres the code that will do what you want (i think)

bear in mind ive only used VARCHAR's which will limit the parameter list, so you can alter the code to suit your needs.

The first parameter is the string you pass into the procedure and the second parameter is the delimiter your string uses.

you would use it like so:

   EXEC TestFunc 'testvalue1, testvalue2, testvalue3', ','

Hope it helps:

CREATE PROCEDURE TestFunc
(
@StringArgs  VARCHAR(255),
@Delim   VARCHAR(1)
)

AS

BEGIN
DECLARE @args   TABLE (args VARCHAR(255))
DECLARE @stringStart INT
DECLARE @stringLength INT
DECLARE @stringValue VARCHAR(255)

SET @stringStart = 0
WHILE @stringStart < LEN(@StringArgs)
BEGIN
  SET @stringLength = (CHARINDEX(@Delim, @StringArgs, @stringStart) - @stringStart)
  IF @stringLength < 0
  BEGIN
   SET @stringLength = (LEN(@stringArgs) - @stringStart) + 1
  END
  SET @stringValue = SUBSTRING(@StringArgs, @stringStart, @stringLength)
  INSERT INTO @args
  VALUES (@stringValue)
  SET @stringStart = (@stringStart + @stringLength) + 1
END

SELECT * FROM some_table WHERE some_column IN (SELECT * FROM @args)
END
Mougenot
actually you would do :

select * from MyTable where TheID in (select value from dbo.Split('2,3', ','))

because

select * from dbo.Split('2,3', ',')) yeilds:

ID          VALUE
----------- ------------
0           1
1           2
2           3

that is the split arguments end up in the 'value' column
Eddie Tse
Why not use dynamic SQL

declare @nvchSQL nvarchar(500)
declare @nvchNumbers nvarchar(50)
set @nvchNumbers = N'486,808,68000'

set @nvchSQL = N'SELECT * FROM someTable WHERE someNumber IN (' + @nvchNumbers + N')'
exec sp_executesql @nvchSQL
bfoster
I would ask myself why there would be need to join or subquery the results from a stored procedure call. Depending on what the situation is we must all make choices after the requirements and performance any design puts us in front of.

For the original question I would have choosen dynamic SQL solution first.

You can create smaller dynamic SQL and load it into temporary tables if you need to work with the result of a stored procedure call.

use master

create table #demo
(
rowtext nvarchar(4000)
)

insert into #demo
exec sp_helptext 'sp_helptext'

select * from #demo where rowtext like '%from%'

drop table #demo
BuffaloUS
Hi friends,

Have you seen this article This discusses about passing a list of values to use in the IN clause.

"How to pass array of values into SQL Server stored procedure using XML and Visual Basic .NET"

http://support.microsoft.com/default.aspx scid=kb;en-us;555266

with regards,

buragohain
chrisjohnson
Lee,

Thanks so much. This is working just fine. I just expected to be able to put that list of numbers in the () for the IN clause.

Thanks Again...
Mike
Jim Vobrak
yes that would work, but in complex cases it can get unwieldly.

you cant use a stored procedure in a subquery or as a join table can you

amal02

or. . . use my split function -

suppose you have a table

MyTable

TheID	Description
1	fu
2	bar
3	fubar

then
select * from MyTable where TheID in (select ID from dbo.Split('2,3', ','))

returns:

TheID	Description
2	bar
3	fubar

you can then parameterize:

select * from MyTable where TheID in (select ID from dbo.Split(@splitargs, @delim)

the IN statement is immune to the string values returned by the split on '2,3' as long as the values can be converted as per SQL2000 rules. . .

heres the split function:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION SPLIT(@S AS varchar(8000), @DELIM AS varchar(8000))
RETURNS @SPLITTABLE TABLE (ID INT IDENTITY(0,1) PRIMARY KEY, VALUE varchar(8000))
AS
BEGIN
DECLARE @TEMP varchar(8000)
DECLARE @THEVALUE varchar(8000)
SET @TEMP = ISNULL(@S, '')
DECLARE @IDX INT
DECLARE @POS INT
SET @IDX= CHARINDEX(@DELIM, @TEMP)
WHILE @IDX >= 1
BEGIN
  SET @POS = @IDX - 1
  SET @THEVALUE = SUBSTRING(@TEMP, 1, @POS)
  SET @POS = @IDX+LEN(@DELIM)
  SET @TEMP = SUBSTRING(LTRIM(RTRIM(@TEMP)), @POS, LEN(@S))
  INSERT @SPLITTABLE (VALUE) VALUES(RTRIM(LTRIM(@THEVALUE)))
  SET @IDX= CHARINDEX(@DELIM, @TEMP)
END
IF @TEMP <> ''
  INSERT @SPLITTABLE (VALUE) VALUES(RTRIM(LTRIM(@TEMP)))
RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

bc020400363
Thanks Blair,

I finally got around to trying it out yesterday and I realized that the values were in the value column.

Pretty cool function, we're going to use it.

Thanks Again,
Mike
Using a parameter for the "IN" clause

Answer this question