Using Parameter (@value) for "IN" function list

[daddygiles]

I am trying to select a group of records based on a parameter value
passed to the db from a web page. The value comes in as @Status and
has a list of statusID's: (1,2,5,9) I've tried to use
"Where table.status IN (Select * from @Status AS ValueList)"
And also tried
"Where table.status IN (@Status)"
And neither worked. Any suggestions?

[Erland Sommarskog]

[posted and mailed, please reply in news]

(da********@yahoo.com) writes:

am trying to select a group of records based on a parameter value

passed to the db from a web page. The value comes in as @Status and
has a list of statusID's: (1,2,5,9) I've tried to use
"Where table.status IN (Select * from @Status AS ValueList)"
And also tried
"Where table.status IN (@Status)"
And neither worked. Any suggestions?

WHERE table.status IN (@status)

is equivalent to

WHERE table.status = @status

More generally

WHERE table.status IN (@val1, @val2, @val3)

is a short-cut for:

WHERE status = @val1 OR status = @val2 OR status = @val3

As for:

Where table.status IN (Select * from @Status AS ValueList)

In this case @status would need to be a table variable with on single
column. (And a table variable is variable of which the datatype is a
table.)

For actually do what you want to do, see a part in an article on my web
site: http://www.sommarskog.se/arrays-in-s...st-of-integers

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[daddygiles]

Kind sir,
I was successful with this method of approach. Thank you very much
once again for your excellent help!

--Chris