Joe,

You could do something like this

'**

Create procedure test1 @variable1 char(2)='', @Variable2 char(2)='', @Variable3 char(2)=''
as
Declare @AllVariable char(50)
if @variable1 <> ''
set @AllVariable = '''' + @variable1 + ''''

if @Variable2 <> ''
set @AllVariable = @AllVariable + ',' + '''' + @variable2 + ''''

if @Variable3 <> ''
set @AllVariable = @AllVariable + ',' + '''' + @variable3 + ''''

Select * from Samples where CusNumber in (@AllVariable)

go

'**

For each variable that did not receive an argument it will be empty and you can test for the argument.
You can use the same method and add many more parameters, then use the if statement for each new parameter to test for emtpy string.

Jeremy Beal
Programmer Analyst
Shamrock Materials, Inc.

-----Original Message-----
From: Horton, Joe (LNI) [mailto:[Email Address Removed] Tuesday, July 22, 2003 2:01 PM
To: LazyDBA.com Discussion
Subject: SP Code


Ok - real easy question, I'm sure - but I can't sort it out.

Below is a code snippet where I want to turn this into a SP, and pass in the
value of @ActivityChoice. I've hard coded the value below to be 'RC' but I
want it to have more values dependant on what was passed.

It can be any combination or just one of the following: RC, I, RI, FO.

DECLARE @ActivityChoice CHAR(2)
SELECT @ActivityChoice = "RC"
--Want to pass in value of @ActivityChoice
--example values could be RC, I, RI, FO

select enfact_stat_code
from enfact
where enfact_stat_code in (@ActivityChoice)

Right now it returns:
enfact_stat_code
----------------
RC
RC
RC
RC
RC
RC
RC

A sample output when I get it working would be, if I passed it 'RC', and
'FO' and 'I"
enfact_stat_code
----------------
FO
FO
RC
I
I
RC
FO
RC
RC
RC
I
FO
RC
RC


__________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

MS Sql Server LazyDBA home page