where column in ( two + values here )

[sramey00]

im trying to search a string a db gather the needed information and then query another db for the records that match the information. i can obtain the first set of data fine.
up to the print line works... looks like 1259,1333

however when i try to use that in the second select statement, i receive an error: Syntax error converting the varchar value '1259,1333' to a column of data type int.
my column is declared as type in and my variable is declared as type varchar. ive been trying to use the replace function to take out the quotes that are put onthe data. yet im having no luck. any suggestions?

declare @test varchar(255)
select @test = replace(substring(page_name, patindex('%255B%', page_name) + 4, len(page_name) - patindex('%255B%', page_name) - 8), '%252C', ',')
from crystallog
where log_time = '20:39:54'
and page_name like '%255B%'
--print @test

select * from salesrptd1.dbo.salesrep
where salesrep_nbr in (@test)

Mr. Steve

 

[JohnDTampaBay]

You will have to use a bit of dynamic SQL. Good luck!

DECLARE @sql nvarchar(1000)

SET @sql = 'select * from salesrptd1.dbo.salesrep
where salesrep_nbr in (' + @test + ')'

PRINT @sql
EXEC(@sql)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[sramey00]

ahh yes good ol'dynamic sql!

thanks for the push, i seemed to have forgotten that trick!

Mr. Steve

 

[ESquared]

sramey00,

I recommend against using dynamic SQL. Instead, check out

faq183-5207 Passing a list of values to a Stored Procedure (Part II)

-------------------------------------
A sacrifice is harder when no one knows you've made it.