Stored Procedure with multi value parameter behaving strangely

Question

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 get the following error message.

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

Is there anything I am missing? Thanks

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 stored 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 message:

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

Answer 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.

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