Declaring multiple string parameter in declare and set variable

Question

@Status1 nvarchar(max) SET @Status1 IN ('AB''AD') SELECT * FROM EMPLOYEE WHERE STATUS IN (@Status1 )

Muhammad Imran Ansari · Answer

Hello SQL Server doesn't interpret the contents of a single string variable as a list when used in an IN clause. Instead you need to split the string into individual values. You can use either STRING_SPLIT function (SQL Server 2016 or later) or Dynamic SQL query to achieve this. Using STRING_SPLIT: DECLARE @Status1 NVARCHAR(MAX) SET @Status1 = 'ABAD' SELECT * FROM EMPLOYEE WHERE STATUS IN (SELECT value FROM STRING_SPLIT(@Status1 '')) Using Dynamic SQL query: DECLARE @Status1 NVARCHAR(MAX) SET @Status1 = '''AB'' ''AD''' -- Manually wrap the values with single quotes for dynamic SQL DECLARE @SQL NVARCHAR(MAX) SET @SQL = N'SELECT * FROM EMPLOYEE WHERE STATUS IN (' + @Status1 + ')' Happy Coding. Thank you!

Tuhin Paul · Answer

If you're working with a comma-separated string you can use a string-splitting function like STRING_SPLIT (available in SQL Server 2016+). DECLARE @Status1 NVARCHAR(MAX); SET @Status1 = 'ABAD'; -- Query using STRING_SPLIT SELECT * FROM EMPLOYEE WHERE STATUS IN (SELECT value FROM STRING_SPLIT(@Status1 '')); see below the execution flow for this query.

Tuhin Paul · Answer

You need to split the values in the string into individual rows that SQL Server can use in the IN clause. DECLARE @Status1 TABLE (StatusValue NVARCHAR(MAX)); -- Insert values into the table INSERT INTO @Status1 (StatusValue) VALUES ('AB') ('AD'); -- Query using IN SELECT * FROM EMPLOYEE WHERE STATUS IN (SELECT StatusValue FROM @Status1); You can declare a table variable to hold the statuses. Execution Plan Breakdown Insert Into @Status1 Table Variable : Operation : Insert rows into the in-memory table variable. Cost : Minimal as table variables are held in memory. Subquery Evaluation ( SELECT StatusValue FROM @Status1 ) : Operation : Scan the @Status1 table variable to extract the values for the IN clause. Execution Plan : This would typically show as a Table Scan because table variables do not have indexes. Main Query Execution ( SELECT * FROM EMPLOYEE WHERE STATUS IN (...) ) : Operation : Match rows in the EMPLOYEE table based on the STATUS column. Execution Plan : Index Seek : If STATUS is indexed SQL Server uses an Index Seek to quickly locate rows matching the values from the subquery. Table Scan : If STATUS is not indexed SQL Server scans the entire EMPLOYEE table to evaluate the condition. Row Matching : SQL Server evaluates the IN clause by comparing STATUS values against the results of the subquery.