Passing multiple string values in a user entered parameter

Question

I'm creating an SSRS and having trouble passing user entered parameter with multiple string values.

I'm having trouble with the 3rd SELECT in the example below. How can I escape the single quote and pass multiple values in both parameters?

Thanks!!

CREATE TABLE dbo.#Cars (   
   Car_id int NOT NULL, 
   Color varchar(10), 
   Brand varchar(10), 
   Code int, 
) 

INSERT INTO dbo.#Cars VALUES(1, 'RED',   'Nissan',     555);
INSERT INTO dbo.#Cars VALUES(2, 'RED',   'BMW',        555);
INSERT INTO dbo.#Cars VALUES(3, 'BLUE',  'Toyota',     555);
INSERT INTO dbo.#Cars VALUES(4, 'GREEN', 'Nissan',     555);
INSERT INTO dbo.#Cars VALUES(5, 'BLACK', 'Alfa Romeo', 555);

SELECT * FROM dbo.#Cars;
SELECT * FROM dbo.#Cars WHERE Color IN ('RED', 'BLUE');

DECLARE @PARAM_COLOR VARCHAR(MAX);
DECLARE @PARAM_BRAND VARCHAR(MAX);
SET @PARAM_COLOR =  '''RED'',''BLUE''';
SET @PARAM_BRAND = '''Nissan'',''BMW''';
SELECT * FROM dbo.#Cars WHERE Color IN (@PARAM_COLOR) AND Brand IN (@PARAM_BRAND);

DROP TABLE dbo.[#Cars]

Answer 1

You could use a table variable like this:

DECLARE @PARAM_COLOR TABLE (COLOR VARCHAR(100));
DECLARE @PARAM_BRAND TABLE (BRAND VARCHAR(100));

INSERT INTO @PARAM_COLOR
VALUES ('RED')
    ,('BLUE')
    ,('GREEN')
    ,('BLACK');

INSERT INTO @PARAM_BRAND
VALUES ('Nissan')
    ,('BMW')
    ,('Toyota');

SELECT *
FROM dbo.#Cars
WHERE Color IN (
        SELECT COLOR
        FROM @PARAM_COLOR
        )
    AND Brand IN (
        SELECT BRAND
        FROM @PARAM_BRAND
        );

---

Side note: your CREATE TABLE has an extra comma in your example after Code int:

CREATE TABLE dbo.#Cars (   
   Car_id int NOT NULL, 
   Color varchar(10), 
   Brand varchar(10), 
   Code int 
)