具有多值参数的存储过程表现奇怪

I created a stored procedure in sql server to feed SSRS to allow it to accept Multiple values. I have created it and when I used it in my report or execute it in sql server I have the following error message. Is there anything i am missing? Thanks

我在sql server中创建了一个存储过程来提供SSRS以允许它接受多个值。我创建了它,当我在报告中使用它或在sql server中执行它时,我有以下错误消息。有什么我想念的吗?谢谢

Msg 207, Level 16, State 1, Line 35
Invalid column name 'London'.

This is my sample data. feel free to create table with it

这是我的样本数据。随意用它创建表

DECLARE @MyTables AS TABLE (ID INT, City VARCHAR(100))
INSERT INTO @MyTables VALUES
(1,'London'),
(2,'Chester'),
(3,'Luton'),
(4,'New York'),
(1,'London'),
(2,'Chester'),
(5,'Paris'),
(5,'Paris'),
(2,'Chester'),
(2,'Chester')
SELECT * FROM @MyTables

This is my code for the dynamic stores procedure

这是我的动态存储过程的代码

CREATE PROCEDURE dbo.CitiesGroup
        @Cities NVARCHAR(Max) -- this are the parameters
    AS
    BEGIN
    DECLARE @sqLQuery VARCHAR(MAX)
    Declare @AnswersTempTable Table
    (  ID  INT,
       City VARCHAR(250)
    )
SET @sqlQuery =
    'SELECT  
    ID,
    City
FROM MyTables
where Convert(nvarchar(Max),City) IN ('+@Cities+')
Insert into @AnswersTempTable
exec (@sqlQuery)
select * from @AnswersTempTable'
END

Thanks

EXEC dbo.CitiesGroup 'London'

Error meg

Msg 207, Level 16, State 1, Line 32
Invalid column name 'London'.

2 个解决方案

#1

There is another way to do this. Instead of passing the values into a dynamic query, why not split the parameter using a function? This article written by Aaron Bertrand demonstrates different ways on how to split string in sql server.

还有另一种方法可以做到这一点。为什么不使用函数拆分参数,而不是将值传递给动态查询?本文由Aaron Bertrand撰写,演示了如何在sql server中拆分字符串的不同方法。

Once you have selected one of the functions, you can simply rewrite your stored procedure without creating a dynamic query inside.

一旦选择了其中一个函数,就可以简单地重写存储过程而无需在其中创建动态查询。

CREATE PROCEDURE dbo.CitiesGroup
    @Cities NVARCHAR(Max) -- this are the parameters
AS
BEGIN
    -- simplified query
    -- write your complex logic here
    SELECT ID, City
    FROM MyTables
    WHERE City IN (SELECT Item FROM dbo.SplitStrings_CTE(@Cities, N',');)
END

Usage:

EXEC dbo.CitiesGroup 'London'
GO

EXEC dbo.CitiesGroup 'London,New York,Paris'
GO

Useful Link:

Split strings the right way – or the next best way

以正确的方式分割弦乐 - 或者是下一个最好的方式

#2

Alternatively, if you don't need to use a stored proc, you can simply put your query directly in the dataset as

或者,如果您不需要使用存储过程,则可以直接将查询直接放在数据集中

SELECT ID, City
   FROM MyTables
   WHERE City IN (@Cities)

There is no need for dynamic sql as SSRS will do this for you. Just makes sure the SSRS parameter name and the Variable in your SELECT statement are identical (case-sensitive)

不需要动态sql,因为SSRS会为您执行此操作。只需确保SELECT语句中的SSRS参数名称和变量相同(区分大小写)

#1