You need to place your SQL into a variable and then use EXEC to 
retrieve the
results:



DECLARE @Rules 		VARCHAR(2000)
DECLARE @SQLSTRING	VARCHAR(2100)

SET @Rules =3D '2119,2121,2122'
SET @SQLSTRING =3D 'SELECT * FROM AppResultNavigation WHERE RuleID IN 
(' +
@Rules + ')'

EXEC @SQLSTRING




=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D
Kyle M. Burns, MCSD, MCT
ECommerce Technology Manager
Centra Credit Union
kburns@c...



-----Original Message-----
From: jrodrigues@s... [mailto:jrodrigues@s...]
Sent: Monday, November 12, 2001 12:16 PM
To: sql language
Subject: [sql_language] Re: Passing comma separated parameter to stored
procedure


> This doesn't work  (i.e. 0 rows returned from the table) :-
>
> declare @Rules VarChar(2000)
> set @Rules=3D'2119,2121,2122'
> select * from AppResultsNavigation where RuleID IN (@Rules)
>
> but this **does** work  (i.e. 3 rows are returned from the table) :-
>
> declare @Rules VarChar(2000)
> set @Rules=3D'2119,2121,2122'
> select * from AppResultsNavigation where RuleID IN (2119,2121,2122)
>
> I want to use the variable @Rules so I can pass it into a stored
procedure.
>
> The problem is that SQL server is looking for the
string "2119,2121,2122"
> in the RuleID column of the AppResultsNavigation table (a string 
which
> doesn't exist), instead of looking for 3 separate rows.
>
> Any ideas how I can make this work ?
>
> Thanks

Hi Peter,

Maybe using the INSTR command it works.

If you are using SQL Server, try:

 declare @Rules VarChar(2000)
 set @Rules=3D',2119,2121,2122,'
select * from AppResultsNavigation where INSTR(@Rules, ',' & RuleID & 
',')
>0

Pay attention that it needs commas before and after each ID.

I=B4m using Oracle and it works.

Take care with the string=B4s lenght...

Best regards.

Juliano Moraes Rodrigues
Analista de Sistemas - FA Consult
juliano.rodrigues@f...
(019) 9719-2417
S=E3o Paulo - Brasil

$subst('Email.Unsub')