Passing Parameter to a Stored Proc

[dimplekv]

Hi Group,

My stored proc is something like this: "select * from authors where state in (@State) and lastname like 'B%'"

the following sql works fine "select * from authors where state in ('CA','PA','NJ') and lastname like 'B%'"

I am unable to send the @state parameter from query analyzer.

Any ideas?

 

[tlbroadbent]

You can't use the parameter in the query in that manner. You'll need to build a dynamic SQL statement and execute it.

-----------------------
Declare @sqlstmnt varchar(1024)

Select @sqlstmnt="select * from authors where state in (" + @State + &quot and lastname like 'B%'"

Exec (@sqlstmnt)
-----------------------

Of course, this assumes that @state contains a string delimited with "'" and "," such as 'CA','PA','NJ'. Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[dimplekv]

Thank You very much.