Programatically creating variables for use with IN statements
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 're开发者_JAVA技巧d'
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).
i think you can use sp_executesql
may work for you
@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.
精彩评论