开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜