Help using IN operator for passed list in parameter

Question

Post reply

Help using IN operator for passed list in parameter

Jeff Steinweber

SSC Eights!

Points: 916
More actions
July 27, 2007 at 9:23 am

#177746

Can anyone tell me why this doesn't work? The underwriter_id is an int. This statement gives me two records which is fine.
select
* from underwriter where unw_type = 'serv' and convert(varchar(10), underwriter_id) in ('65177','85709')

This does not work:
declare @isps varchar(1000)
select
@isps = char(39) + '65177' + char(39) + ',' + char(39) + '85709' + char(39)
select * from underwriter where unw_type = 'serv' and convert(varchar(10), underwriter_id) in (@isps)
This statememt gives me the string I need
select
@isps
I get the value '65177','85709' with the quotes included but when it is used in the above statement, I don't get any results.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You probably need to use dynamic sql in this case. I was looking at BOL, and was curious, what if you try this:

in ((select @isps))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

How would that return a dataset instead of a varchar(?)?

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 3

I am not sure what you are asking me... Can you elaborate?

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 4

I tried what you suggested but still no luck. I suspect what I am getting is ''65177','85709'' as the string since the parameter is declared as varchar(1000).

I also tried declaring the parameter as a table and inserting values into the parameter but then I cannot pass the resultset anywhere.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Like I said earlier, looks like you will need to use dynamic sql. Build your query into a string, @SQLCmd = ...

then execute that, exec (@SQLCmd)

I was just hoping that the select in the () would return a dataset that the IN would then use.

Ray M SSC-Insane Points: 21093 More actions · Answer 6

-- Don't use Dynamic sql unless you have to.

-- Try a UDF

Create FUNCTION udf_list_parse_comma_string

(@String varchar(8000))

RETURNS @stringTable TABLE

(string varchar(100))

AS

BEGIN

-- Make sure last char is a comma

if right(@String,1) <> ','

set @String = @String + ','

Declare @a int

, @b-2 int

, @value varchar(100)

select @a = 1

while @a < len(@String)

begin

set @value = ltrim(substring(@String,@a,charindex(',',@String,@a)-@a))

set @a = charindex(',', @String,@a) + 1

INSERT @stringTable (string)

values (@value)

Set @value = NULL

End

RETURN

END

GO

declare @isps varchar(1000)

select @isps = char(39) + '65177' + char(39) + ',' + char(39) + '85709' + char(39)

select * from underwriter where unw_type = 'serv' and convert(varchar(10), underwriter_id) in (select string from dbo.udf_list_parse_comma_string (@isps))