Using IN operator on parameter with list of numbers 1

[dokken]

I would like to used a stored procedure to pass in a list of numbers (Status codes). I have tried:
@Status varchar(10)
As
select distinct status from tblTable
where status in (@Status)

I have tried changing status from an integer to a 1 char field.

Using an exec statement is not an option.

Thanks,
Paul

 

[mookie]

You could use the like operator, something like

declare @Status varchar(10)
select @Status = '[135]'

select distinct status
from tblTable
where convert(char(1), status) like @Status

the above query would return the status of 1,3, and 5

 

[tlbroadbent]

Which version of SQL Server are you running? Is the string, @status, comma-delimited?

Why the prohibition of EXEC? Creating and executing a dynamic query may be the only alternative you have. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[dokken]

The list of number is comma delimited. The reason I con't want to use an exec statement is the whole query is about 200 lines long and it would make it hard to maintain.

Thank,
Paul

 

[tlbroadbent]

I think creating and executing a dynamic SQL statement would not add much overhead. However, I do have a solution that doesn't require an EXEC.

-- Set nocount on to inhibit
-- rows affected messages
set nocount on

-- Assume the variable @status is
-- declared in the SP definition
If Len(@status)=0
Begin
Print 'No input'
Return
End

-- Decalre additional variables for
-- proecedure that follows
Declare @p1 int, @p2 int, @s int

-- Create a temporary to hold the
-- values parsed from @status
Create table #t1 (Stat int)

-- Initialize character position variables
Set @p1=1

-- Find first comma
Set @p2=charindex(',',@status,@p1)

-- Parse @status and insert values into #t1
If @p2=0
-- Handle case of single value
Set @s=cast(@status As Int)
Else
Begin
While @p2>0
Begin
-- get integer value from list
Set @s=cast(substring(@status,@p1,@p2-@p1) As Int)
-- Insert integer into table
Insert #t1 Values (@s)
-- Increment variable
Set @p1=@p2+1
-- Find next comma
Set @p2=charindex(',',@status,@p1)
End
-- Handle last value in list
Set @s=cast(right(@status,len(@status)-@p1+1) As Int)
-- Insert last value in table
Insert #t1 Values (@s)
End

-- Select values from permanent table
-- that exist in the temporary table.
Select * From tblTable
Where Status In (Select stat From #t1)

Let me know if you have any questions. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[dokken]

Shouldn't this work:

Alter Procedure [dbo].getTests
@SampleID int = 167,
@Status varchar(10) = '1, 2, 3'
As
SELECT *
FROM tblTests
WHERE SampleID = @SampleID and
Status in (@Status)

It give me the following error: Syntax error converting the varchar value '1, 2, 3' to a column of data type int.

 

[tlbroadbent]

No, it will not work. You are back to where you began and the same rules still exist. SQL will not allow the use of a variable in the IN statement. If you use dynamic SQL, you can use the variable to create the SQL statement and then execute it because it will have values not variables.

This will work...

Alter Procedure [dbo].getTests
@SampleID int = 167,
@Status varchar(10) = '1, 2, 3'
As

DECLARE @sql nvarchar(4000)

SET @sql=
'SELECT * ' +
'FROM tblTests ' +
'WHERE SampleID = ' + str(@SampleID) +
' AND Status In (' + @Status + ')'

Exec(@sql) Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm