Hi Pete,

Here's a couple of suggestions, and the one to use depends on whether
you are using SQL2K or SQL7.

SQL2K Version.
The easiest way is to pass the parameters as XML. In this format,

@val="<R><D Type='1'></D><D Type='2''></D><D Type='3'></D></R>"

The proc will look like:

CREATE PROCEDURE GetData
@UserName VARCHAR(30),
@CompanyID INT,
@val VARCHAR(500)

declare @i int

exec sp_xml_preparedocument @i output, @val

SELECT Description from Table1
Where Type in (SELECT Type FROM OPENXML (@i,'/R/D', 1) WITH (Type
INTEGER))

EXECUTE sp_xml_removedocument @i


SQL7 Version:
SQL 7 does not have the same XML support. However it is possible to pass
a string of delimited parameters to the procedure and use the string
manipulation features to simulate 'in'.

In this input format:
@val="|1|2|3|" Note the pipe delimiters

CREATE PROCEDURE GetData
@UserName VARCHAR(30),
@CompanyID INT,
@val VARCHAR(500)


SELECT Description from Table1
Where CHARINDEX('|' + Type + '|', @val)>0

Hope that helps.

Dave

-----Original Message-----
From: Pete Ade [mailto:[Email Address Removed]
Sent: 07 August 2002 20:21
To: [Email Address Removed] String of parameters



Hello.

Is it possible to pass a string of parameters to a stored procedure that

will be use for IN operator.
Example. sp_Test 'Test','Test1','Test2'

@val will hold the three parameters I passed to sp_Test

and inside the code you have somehting like.
Select Description from Table1
Where Type in(@val)

I want to do it this way because there is no way to know in advance the
number of values to pass and I dont want to use dynamic sql Thanks.



_________________________________________________________________
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com


---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page