Problem with SELECT...WHERE IN procedure

This works!
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
--
But when I do the same in a proc it doesn't!
create procedure mytest
@.test varchar(1000)
as
SELECT au_lname, state
FROM authors
WHERE state IN (@.test)
go
Call proc:
exec test 'CA, IN, MD'
I get no error messages, just no results!
Whats wrong?
Regards
/AndersHave a look at
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Anders" <it99ama@.du.se> wrote in message
news:O5GLses2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This works!
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> --
> But when I do the same in a proc it doesn't!
> create procedure mytest
> @.test varchar(1000)
> as
> SELECT au_lname, state
> FROM authors
> WHERE state IN (@.test)
> go
> Call proc:
> exec test 'CA, IN, MD'
> I get no error messages, just no results!
> Whats wrong?
> Regards
> /Anders
>
>
>|||Anders (it99ama@.du.se) writes:
> This works!
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> --
> But when I do the same in a proc it doesn't!
> create procedure mytest
> @.test varchar(1000)
> as
> SELECT au_lname, state
> FROM authors
> WHERE state IN (@.test)
> go
> Call proc:
> exec test 'CA, IN, MD'
> I get no error messages, just no results!
> Whats wrong?
Had there been a row with where state had had the value "CA, IN, MD",
you would have found that row.
Have a look at
http://www.sommarskog.se/arrays-in-...list-of-strings
for way to handle a list of values.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||http://www.aspfaq.com/2248
"Anders" <it99ama@.du.se> wrote in message
news:O5GLses2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This works!
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> --
> But when I do the same in a proc it doesn't!
> create procedure mytest
> @.test varchar(1000)
> as
> SELECT au_lname, state
> FROM authors
> WHERE state IN (@.test)
> go
> Call proc:
> exec test 'CA, IN, MD'
> I get no error messages, just no results!
> Whats wrong?
> Regards
> /Anders
>
>
>