Declare variable for in statement in where clause 1

[jmj]

I have a series of queries that I need to run based on an in statement in the where clause. I need to set the variable at the beginning of the script.
No matter how I declare it I get
"Syntax error converting the varchar value ' 4,15,22,23,26,28,32,39' to a column of data type int."

I have tried the following:
DECLARE @Ad varchar(100)
SET @Ad=4,15,22,23,26,28,32,39'

WHERE (bldtype='cl' And tr.AdID in (@Ad))

I have added cast as integer to the where clause without any luck. Does anyone have any ideas how I can do this? I figure it is going to be something easy that I overlooked. Thanks, J
Thanks.

 

[AtomicWedgie]

'4','15','22','23','26','28','32','39'

 

[SQLDenis]

you will need dynamic SQL for or split function


DECLARE @Ad varchar(100)
SET @Ad='4,15,22,23,26,28,32,39'
declare @SQL varchar(500)
set @SQL ='SELECT * from table where (bldtype=''cl'' And tr.AdID in ( ' + @Ad + '))'

print @SQL -- to test
exec (@SQL)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

faq183-5207

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom