Programatically creating variables for use with IN statements.

Question

Please consider the following scripts.

Create Table:

IF OBJECT_ID('Colortable') IS NOT NULL
  DROP TABLE ColorTable

CREATE TABLE Colortable (Color VARCHAR(32))
GO

Insert some values:

SET  NOCOUNT ON

INSERT Colortable
  SELECT 'red'

INSERT Colortable
  SELECT 'orange'

INSERT Colortable
  SELECT 'blue'

INSERT Colortable
  SELECT 'green'
GO

Create my Variable (which will become a paramter in SSRS) automatically:

DECLARE @colors VARCHAR(1024)

SELECT @colors =
         COALESCE(
           @colors + '''' + ',' + '''', '') +
         Color
FROM Colortable

When I use "Select @colors" I get the following:

'red','orange','blue','green'

However, my queries do not work as expected.

SELECT *
FROM colortable
WHERE Color IN ('red', 'orange', 'blue', 'green') -- Returns 4 rows. 


SELECT *
FROM colortable
WHERE Color IN (@colors) -- Returns 0 Rows

Can anyone tell me why? I am trying to generate a string of values so that this script will work in SSRS and SSMS (or whatever tool I am using).

Answer 1

@colors is a single varchar, not a list of varchars. What you can do instead is insert your values into a temp table and join against that.

Answer 2

i think you can use sp_executesql may work for you