trying to pass a variable into an IN statement 1

[christer99]

I am having a problem passing a variable into an IN statement, such as WHERE convert(nvarchar(3),opid) in ( @param10). Any help would be appreciated?


declare @param1 DATETIME, @param2 VARCHAR(3), @param4 VARCHAR(4000), @param5 MONEY, @param6 VARCHAR(50), @param7 VARCHAR(20), @param8 INT, @param9 INT, @param10 VARCHAR(50)


set @param1='5/18/2007 16:40'
set @param2='FG4'
set @param4='aaa'
set @param5= 0
set @param6='CHU'
set @param7='Declined'
set @param8='8'
set @param9='1'
set @param10='183|, |182|, |184|,|175'

set @param10=''''+replace(@param10,'|','''')+''''

select @param10

UPDATE OPMarketing
set DESCRIPTION= @param4, QUOTE= @param5, MARKET= @param6, STATUS= @param7, BCOID= @param8, UWRITID= @param9
WHERE convert(nvarchar(3),opid) in ( @param10)

 

[xanra]

What you can do is create a function such as the following then

create
function [dbo].[CSVtoData](@Str varchar(7000))
returns @t table (numberval int, stringval varchar(100), DateVal datetime)
as
begin

declare @i int;
declare @c varchar(100);

set @Str = @Str + ','
set @i = 1;
set @c = '';

while @i <= len(@Str)
begin
if substring(@Str,@i,1) = ','
begin
insert into @t
values (CASE WHEN isnumeric(@c)=1 THEN @c else Null END,
rtrim(ltrim(@c)),
CASE WHEN isdate(@c)=1 then @c else Null END)
set @c = ''
end
else
set @c = @c + substring(@Str,@i,1)
set @i = @i +1
end
return
end

then for your parameter all you would do is something like ...
(nvarchar(3),opid) in ( (select StringVal from dbo.CSVtoData( @param10))


try it out in a test environment. hth