Below is a post from the previous Wrox P2P group about this issue. You need to modify the inList function to return CHAR or VARCHAR, but it should work for you.

Brian Freeman
(770) 916-0595 ext. 415
Carnegie Technologies/Bluewave Computing
www.carnegie.com and www.bluewave-computing.com

Try this SQL Server 2000 user defined function. It takes a comma separated
string as input, and returns a table variable.

You can then use it as:

SELECT * FROM myTable WHERE myField IN (SELECT val FROM Inlist(@argument))

Note that this function as written assumes the argument contains a comma
separated list of integers; I'm sure you can adapt it for your own use.

-------------
CREATE FUNCTION Inlist (@list varchar(8000))
RETURNS @tbl TABLE (val int not null) AS
BEGIN
Declare @index int,
@pos int,
@str varchar(8000),
@num int
Set @pos = 1
Set @index = 1
While @index > 0
Begin
set @index = charindex(',', @list, @pos)
if @index > 0
Set @str = substring(@list, @pos, @index - @pos)
Else
Set @str = substring(@list, @pos, Len(@list))
Set @str = ltrim(rtrim(@str))
Set @num = cast(@str as integer)
Insert @tbl (val) values (@num)
Set @pos = @index + 1
End
Return
End
--
Jeff Mason Custom Apps, Inc.
[Email Address Removed] Jeremy wrote:
> Joe,
>
> If you are wanting to hard code it, then
>
> declare @Abc char(34)
> set @abc = '''RC''' + ',' + '''I'''
>
> select enfact_...
>
> '**
>
> Select @ActivityCoice = ... is assumming you are looking for column
> names to store into variable.
>
> Jeremy Beal
> Programmer Analyst
> Shamrock Materials, Inc.
>
> -----Original Message-----
> From: Horton, Joe (LNI) [mailto:[Email Address Removed] Sent: Tuesday, July 22, 2003 2:52 PM
> To: Beal, Jeremy; LazyDBA.com Discussion
> Subject: RE: SP Code
>
>
> The problem though is while this will work:
> Select enfact_stat_code from enfact where enfact_stat_code in
> ('RC','I')
>
> This will not work:
> DECLARE @ActivityChoice CHAR(32)
> SELECT @ActivityChoice = "'RC', 'I'"
> Select enfact_stat_code from enfact where enfact_stat_code in
> (@ActivityChoice)
>
> __________________________
> Joe Horton
> Database Developer / Software Engineer
> WISHA/Legal Services Software Development
> Department of Labor and Industries
> Voice (360) 902-5928 fax (360) 902-6200
>
>
> -----Original Message-----
> From: Beal, Jeremy [mailto:[Email Address Removed] Sent: Tuesday, July 22, 2003 2:35 PM
> To: Horton, Joe (LNI); LazyDBA.com Discussion
> Subject: RE: SP Code
>
> 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] Sent: 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
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page