In SQL Server, for writing secure and efficient queries, especially when dealing with a dynamic list of values, parameterizing an SQL IN Clause is essential. This can be achieved using Table-Value Parameters, STRING_SPLIT, and dynamic SQL. In this blog, let us explore the different methods that can be used to parameterize an SQL IN Clause with examples for each in detail.
Table of Contents:
What is the IN Clause in SQL?
The IN Clause in SQL can be used to check if a column’s value matches with any value in a given list.
Syntax:
SELECT * FROM TableName
WHERE ColumnName IN (Value1, Value2, Value3, ...);
Example:
-- To create a Product table
CREATE TABLE Products (
ProductID INT PRIMARY KEY IDENTITY(1,1),
ProductName VARCHAR(100),
Category VARCHAR(50),
Price DECIMAL(10,2)
);
-- Insert some values into it
INSERT INTO Products (ProductName, Category, Price) VALUES
('Laptop', 'Electronics', 1200.00),
('Smartphone', 'Electronics', 800.00),
('Table', 'Furniture', 150.00),
('Chair', 'Furniture', 75.00),
('Headphones', 'Electronics', 200.00),
('Sofa', 'Furniture', 500.00);
-- Using IN Clause to filter the Products
SELECT Category, Price FROM Products
WHERE Category IN ('Electronics', 'Furniture');
Output:
Explanation: Here, the SELECT statement returns all the Products that belong to the Electronics and Furniture category.
Why do we need to parameterize the SQL IN Clause?
Parameterizing the SQL IN Clause is essential for security and performance. SQL injection attacks can be prevented by ensuring that user inputs are considered data rather than executable queries. It also increases the performance by reusing the query and reducing the database overhead. Without modifying the query structure, dynamic filtering makes it easier to pass a list of values. In SQL Server, for safe and efficient query execution methods like Table-Valued Parameters, STRING_SPLIT can be used.
Advantages of Parameterizing an SQL IN Clause
- Query Performance Increases: It allows query reusing. It also reduces the execution time and database overhead.
- Ensures Data Integrity: Rather than treating input data as a concatenated string, it is treated correctly as an individual string
- Prevents SQL Injection: Ensures that user inputs are treated as data, which reduces the security risks.
- Increases Code Maintainability: Makes queries more readable, reusable, and easier to debug
Methods to Parameterize an SQL IN Clause
To improve security, maintainability, and performance, parameterizing the SQL IN Clause is an essential technique. Some of the methods include Table-Valued Parameters, STRING_SPLIT, and dynamic SQL.
Before getting started with the methods, let us create a Projects table and insert some values into it.
-- To create a Project table
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY IDENTITY(1,1),
ProjectName VARCHAR(100),
Status VARCHAR(20),
Budget DECIMAL(10,2)
);
-- To insert values into it
INSERT INTO Projects (ProjectName, Status, Budget) VALUES
('AI Research', 'Ongoing', 50000.00),
('Cloud Migration', 'Completed', 75000.00),
('Cybersecurity Upgrade', 'Ongoing', 60000.00),
('Mobile App Development', 'Pending', 45000.00),
('Data Analytics', 'Completed', 80000.00),
('E-commerce Platform', 'Ongoing', 90000.00),
('IoT Integration', 'Pending', 55000.00);
-- To display the table
Select * from Projects;
This is how the table looks once it is created and inserted with the values.
Method 1: Using Table-Valued Parameters in SQL Server
In SQL Server, to handle dynamic lists in an IN Clause more efficiently, this method allows passing a table as a parameter.
Syntax:
CREATE PROCEDURE GetProjectsByStatus
@Statuses ProjectStatusType READONLY
AS
BEGIN
SELECT * FROM Projects
WHERE Status IN (SELECT Status FROM @Statuses);
END;
GO
Example:
GO
-- To Create a Table Type for Project Status
CREATE TYPE ProjectStatusType AS TABLE (Status VARCHAR(20));
GO
-- To Create a Stored Procedure using (TVP)
CREATE PROCEDURE GetProjectsByStatus
@Statuses ProjectStatusType READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Projects
WHERE Status IN (SELECT Status FROM @Statuses);
END;
GO
-- Execute the query using TVP
DECLARE @StatusTable ProjectStatusType;
INSERT INTO @StatusTable (Status) VALUES ('Ongoing'), ('Completed');
EXEC GetProjectsByStatus @StatusTable;
Output:
Explanation: The ProjectStatusType is created to hold the status values. Thus, the SELECT Statement retrieves the project with the given status in the IN Clause.
Method 2: Using STRING_SPLIT in SQL Server
This function is mainly used to break the comma-separated string into multiple values, which can be used in an IN Clause.
Syntax:
CREATE PROCEDURE ProcedureName
@ParameterName VARCHAR(MAX)
AS
BEGIN
SELECT * FROM TableName
WHERE ColumnName IN (SELECT value FROM STRING_SPLIT(@ParameterName, ','));
END;
GO
Example:
GO
CREATE PROCEDURE GetProjectsByStatus
@StatusList VARCHAR(MAX)
AS
BEGIN
SELECT * FROM Projects
WHERE Status IN (SELECT value FROM STRING_SPLIT(@StatusList, ','));
END;
GO
EXEC GetProjectsByStatus 'Completed';
Output:
Explanation: The Stored Procedure ‘GetProjectsByStatus’ accepts a comma-separated list and retrieves only the matching projects using STRING_SPLIT. Thus, only the status with ‘Completed’ is displayed.
Method 3: Using XML or JSON Parsing in SQL Server
In this method, instead of a comma-separated string, XML parsing allows passing multiple values efficiently as structured data.
Syntax:
GO
CREATE PROCEDURE ProcedureName_XML
@ParameterName XML
AS
BEGIN
SELECT * FROM TableName
WHERE ColumnName IN (
SELECT x.value('.', 'DataType')
FROM @ParameterName.nodes('/Root/ElementName') AS T(x)
);
END;
GO
Example:
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE GetProjectsByStatus_XML
@StatusList XML
AS
BEGIN
SET QUOTED_IDENTIFIER ON;
SELECT ProjectID, Status, Budget FROM Projects
WHERE Status IN (
SELECT x.value('.', 'VARCHAR(20)')
FROM @StatusList.nodes('/Statuses/Status') AS T(x)
);
END;
GO
EXEC GetProjectsByStatus_XML '<Statuses><Status>Ongoing</Status><Status>Completed</Status></Statuses>';
Output:
Explanation: The XML input has two status values (‘Ongoing’, and ‘Completed’). The query filters out the tables based on those statuses.
Method 4: Using Temporary Table in SQL Server
A temporary table (#TempTable) can be used to store multiple values and filter the results efficiently. This method also improves the performance of parameterizing the SQL IN Clause.
Syntax:
GO
CREATE PROCEDURE ProcedureName
AS
BEGIN
CREATE TABLE #TempTable (ColumnName DataType);
INSERT INTO #TempTable (ColumnName) VALUES (Value1), (Value2), (Value3);
SELECT * FROM TargetTable
WHERE TargetColumn IN (SELECT ColumnName FROM #TempTable);
DROP TABLE #TempTable;
END;
GO
Example:
GO
CREATE PROCEDURE GetProjectsByStatus_TempTable
AS
BEGIN
CREATE TABLE #TempStatus (Status VARCHAR(20));
INSERT INTO #TempStatus (Status) VALUES ('Ongoing'), ('Completed');
SELECT * FROM Projects
WHERE Status IN (SELECT Status FROM #TempStatus);
DROP TABLE #TempStatus;
END;
GO
EXEC GetProjectsByStatus_TempTable;
Output:
Explanation: Here, the Stored Procedure creates a TemporaryTable (#TempStatus) that inserts two status values (Ongoing, Completed) into it, and then retrieves the projects with those Statuses.
Method 5: Using Common Table Expression in SQL Server
In this method, the values are dynamically generated, and this CTE can be used to store multiple values, and the results can be filtered efficiently.
Syntax:
WITH CTE_Name (ColumnName) AS
(
SELECT Value1 UNION ALL
SELECT Value2
)
SELECT * FROM TargetTable
WHERE TargetColumn IN (SELECT ColumnName FROM CTE_Name);
Example:
WITH StatusCTE (Status) AS
(
SELECT 'Pending' UNION ALL
SELECT 'Ongoing'
)
SELECT * FROM Projects
WHERE Status IN (SELECT Status FROM StatusCTE);
Output:
Explanation: Here, the CTE named StatusCTE is used to hold status values (‘Pending’, ‘Ongoing’) and also filter and return rows that only match CTE.
Alternative methods to parameterize an SQL IN Clause
Apart from the above-discussed methods, there are a few alternative methods that can be used to parameterize an SQL IN Clause.
Method 1: Using Dynamic SQL
In Dynamic SQL, the SQL statements are dynamically constructed as a string and are executed at runtime using EXEC.
Syntax:
DECLARE @ParameterName NVARCHAR(MAX) = 'Value1, Value2';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM TargetTable WHERE TargetColumn IN (' + @ParameterName + ')';
EXEC sp_executesql @SQL;
Example:
DECLARE @StatusList NVARCHAR(MAX) = '''Ongoing'', ''Completed''';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Projects WHERE Status IN (' + @StatusList + ')';
EXEC sp_executesql @SQL;
Output:
Explanation: Here, the Dynamic SQL Statements are constructed by concatenating @StatusList into an IN Clause.
Method 2: Using STRING_AGG() for Dynamic IN Clause
This method is useful for comma-separated lists from different sources that concatenate multiple values into a single string used in Dynamic SQL.
Syntax:
DECLARE @ParameterList NVARCHAR(MAX);
-- Concatenate multiple values into a single string
SELECT @ParameterList = STRING_AGG(ColumnName, ',')
FROM (VALUES (Value1), (Value2)) AS T(ColumnName);
-- Construct and execute dynamic SQL
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM TargetTable WHERE TargetColumn IN (' + @ParameterList + ')';
EXEC sp_executesql @SQL;
Example:
DECLARE @StatusList NVARCHAR(MAX);
-- Concatenate multiple status values into a single string with single quotes
SELECT @StatusList = STRING_AGG(QUOTENAME(Status, ''''), ',')
FROM (VALUES ('Ongoing'), ('Completed')) AS T(Status);
-- Construct and execute dynamic SQL
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Projects WHERE Status IN (' + @StatusList + ')';
-- Debugging: Check the final query
EXEC sp_executesql @SQL;
Output:
Explanation: Here, the STRING_AGG concatenates multiple status values into a single comma-separated string.
Performance Comparison of Each Method
|
Method |
Use Case |
Pros |
Cons |
| Using Table-Valued Parameters | This is used when we need to parse large lists efficiently | Performance-optimized and effective with indexed tables | It is read-only and cannot modify data inside the TVP |
| Using STRING_SPLIT | Useful for Comma-separated values | It is easy to use and there is no need for extra space | It only works well with single-column values |
| Using XML or JSON parsing | Beneficial for usage in applications passing structured data | Handles complex data | Slower than TVP because of overhead in processing |
| Using Temporary Table | Useful when applications are dealing with temporary datasets | Tables are indexed for better performance | Extra overhead is required for the creation and insertion |
| Using CTE | Used when hierarchical queries are involved | Does well with recursive queries | Performance decreases on large datasets |
Real-world Examples
1. E-commerce: This e-commerce site allows customers to filter their orders by multiple statuses such as ‘Pending,’ ‘Shipped,’ and ‘Delivered.’
Example:
-- Create Orders Table
CREATE TABLE Orders (OrderID INT, CustomerName VARCHAR(50), Status VARCHAR(20));
-- Insert Sample Data
INSERT INTO Orders VALUES (1, 'Anand', 'Pending'),
(2, 'John', 'Shipped'),
(3, 'Vijay', 'Delivered'),
(4, 'Joseph', 'Cancelled');
-- To Create TVP
GO
CREATE TYPE StatusTableType AS TABLE (Status VARCHAR(20));
GO
-- To Create a Stored Procedure
CREATE PROCEDURE GetOrdersByStatus
@Statuses StatusTableType READONLY
AS
BEGIN
SELECT * FROM Orders WHERE Status IN (SELECT Status FROM @Statuses);
END;
GO
DECLARE @StatusList StatusTableType;
INSERT INTO @StatusList VALUES ('Pending'), ('Shipped');
EXEC GetOrdersByStatus @StatusList;
Output:
Explanation: The SELECT statement retrieves all the orders in which the status matches with any value from the Table-Valued parameter ‘@Statuses.’
2. College Management System: The College Management system wants to retrieve only the students from specific departments.
Example:
-- Create Employee Table
CREATE TABLE Employees (EmpID INT, Name VARCHAR(50), Department VARCHAR(20));
-- Insert Sample Data
INSERT INTO Employees VALUES (1, 'John', 'TCW'),
(2, 'Emma', 'TRA'),
(3, 'Mike', 'HR'),
(4, 'Sophia', 'BDA');
-- Using STRING_SPLIT to Filter Employees by Department
DECLARE @DepartmentList NVARCHAR(MAX) = 'BDA,TCW';
SELECT * FROM Employees
WHERE Department IN (SELECT value FROM STRING_SPLIT(@DepartmentList, ','));
Output:
Explanation: Using STRING_SPLIT, this query dynamically filters employees with specific departments.
Best Practices
- Avoid Concatenating the string directly: Always use sp_executesql to prevent the SQL injection risks.
- Choose the right method: To improve the performance, use STRING_SPLIT for small lists and Temporary Tables for large datasets.
- Use JSON/XML parsing: This parsing method is useful for API applications, but it has to be carefully handled due to passing overhead.
- Optimize queries: To improve the performance of the query, ensure that IN Clause columns are indexed properly.
Conclusion
Parameterizing the IN Clause in SQL is essential for efficiency and security. Different methods such as Table-Valued Parameters, STRING_SPLIT, and Temporary Tables can be used to parameterize an SQL IN Clause based on its advantages and use cases. In this blog, you have gained knowledge about different methods in which we can parametrize an SQL IN Clause.
To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.
How to Parameterize an SQL IN Clause – FAQs
1. Why do we need to parameterize the SQL IN Clause?
We need to parameterize an SQL IN Clause because it prevents SQL Injection and improves performance.
2. Which is the best method to pass multiple values to the IN Clause?
We can use STRING_SPLIT for small lists and Temporary Tables for large datasets.
3. Is it possible to pass multiple values in the IN Clause using JSON?
Yes, we can use OPENJSON or Nodes() for comma-separated values.
4. Why am I getting the error ‘CREATE PROCEDURE’ must be the first statement in a batch.
To avoid this error, ensure that CREATE PROCEDURE starts a new batch by using GO before it.
5. Which method do I need to use for the best performance?
TVP is the most efficient method for large datasets.