a list as a variable

[KevinFSI]

This works:

SELECT *
FROM myTable
WHERE myCol IN (1,2,3,4)

However, this does not:

DECLARE @myVar varchar(8)
SET @myVar = '1,2,3,4'

SELECT *
FROM myTable
WHERE myCol IN (@myVar)

Can any of you help on this one? I'm getting something like, "error converting varchar '1,2,3,4' to int." What gives? Kevin
slanek@ssd.fsi.com

 

[tlbroadbent]

@myvar contains a string and it cannot be compared to an integer. You cannot use variables in certain part of a query. The criteria of an IN clause is one of those forbidden places.

The only way to do what you want is to dynamically create a SQL statement and execute it. Try this SQL script.

DECLARE @myVar varchar(8), @sql nvarchar(400)
SET @myVar = '1,2,3,4'

SET @sql=
'SELECT *
FROM myTable
WHERE myCol IN (' + @myVar + ')'

Execute @sql

The reason this works is because the value of @myar is evaluated and inserted into the SQL statement. Then the SQL statement is executed. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[KevinFSI]

This query is a stored procedure that is being called by a ColdFusion template. The variable is a value that is selected by the user. Would your suggestion work in this scenario?

Thanks Kevin
slanek@ssd.fsi.com

 

[tlbroadbent]

Yes! You use the same code except the parameter @myvar would be passed to the SP upon execution. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin