Declaring multiple string parameter in declare and set variable

Guest User

1y753SQL

@Status1 nvarchar(max)
SET @Status1 IN ('AB','AD')

SELECT * FROM EMPLOYEE WHERE STATUS IN (@Status1 )

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari1y ago
Accepted 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 = 'AB,AD'

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 Paul1y ago
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 = 'AB,AD';

-- 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 Paul1y ago
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
1. Insert Into @Status1 Table Variable:
  
  Operation: Insert rows into the in-memory table variable.
  
  Cost: Minimal, as table variables are held in memory.
2. 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.
3. 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.
4. Row Matching:
  
  SQL Server evaluates the IN clause by comparing STATUS values against the results of the subquery.