How to Parameterize an SQL Server IN clause

SQL Server IN Clause is used to filter data based on a set of values provided. The IN clause can be used instead of using multiple OR conditions to filter data from SELECT, UPDATE, or DELETE query. The IN clause with parameterized data mainly inside Stored Procedures helps filter dynamic data using SQL Queries efficiently.

In this article, we will discuss in detail about methods of how parameterized values are passed to the IN clause with examples.

Parameterize IN clause

Parameterizing data sent to SELECT queries is an efficient method for handling dynamic data instead of only using static data. We can also Parameterize the IN clause values in SQL Server queries when using the WHERE clause of a SELECT query, UPDATE query, or DELETE query. This is particularly useful when we want to filter results based on multiple values without having to explicitly write each value in the IN clause.

Ways to Parameterize IN clause

Let's understand the various ways to Parameterize IN clause in SQL Server with the explanation of queries and output also.

Method 1: Variables as Parameters

We can send multiple values using Parameterized variables in a Stored Procedure which can be used in an IN clause.

Let's understand using the example below:

Example of Variables as Parameters

We will used Employees Table for better understanding of examples and methods. Table looks like:

Create the stored procedure:

Query:

CREATE PROCEDURE FindTechnolgyRecords
 @Techno1 varchar(50),
    @Techno2 varchar(50),
    @Techno3 varchar(50)
AS
BEGIN
Select * from Employees where Technology IN (@Techno1,@Techno2,@Techno3)
END
GO

Explanation: In the above example, we are creating a stored procedure with 3 parameters, which will receive dynamic values when the stored procedure is executed. These 3 variables are placed inside the IN Clause, which will filter data for the Technology column for the Employees table in our example.

Execute the Stored Procedure with values:

EXEC FindTechnolgyRecords @Techno1 ='JAVA',@Techno2='ASP.NET',@Techno3='React Native'

In the above exec stored procedure call we are sending 3 values and based on these 3 values the IN clause will filter data from the 'Employees' table based on data available in 'Technology' column.

The scope of using multiple variables is limited as we can send limited number of variables with data.

Output:

Explanation: In the above output we can see records with Technology having the values send as parameters such as 'JAVA','ASP.NET','React Native'.

Method 2: STRING_SPLIT

The STRING_SPLIT function can be used to extract strings in a variable combined by a separator. For example, a list of Technology names can be stored in a string variable using comma ',' separator and then inside the IN clause the values can be split using the STRING_SPLIT function.

Syntax:

STRING_SPLIT (string, separator)

Explanation: String_Split function takes 2 arguments and first argument is 'String' which is a string variable and 'Separator' is the string separator like comma, semicolon or '|' symbol.

Below is an example of how the STRING_SPLIT function works when used to Parameterize the IN clause.

Example of STRING_SPLIT

Create the Stored procedure with STRING_SPLIT function.

Query:

CREATE PROCEDURE SPLIT_INClause
    @Technos varchar(500)
AS
BEGIN

Select * from Employees where Technology IN (SELECT value FROM STRING_SPLIT(@Technos,','))

END
GO

Explanation: In the above stored procedure, there is a string parameter used to pass multiple string values to a strored procedure, which can be used inside the IN clause to check for multiple values in the specified column in a table. The STRING_SPLIT function fetches the individual values from the string variable by splitting the single string based on the separator provided.

Execute the stored procedure with sample data as below:

Query:

EXEC SPLIT_INClause

Output:

Explanation: In the above output we can see 4 records which has multiple values passed to the stored procedure in a single parameter, but was split by the STRING_SPLIT.

Method 3: TABLE TYPE

User define Table Type is a user-defined type that can be used in stored procedure to declare table-valued parameters. The Table-valued parameter will act like a table using this we can pass list of values or records to the stored procedure.

Syntax:

CREATE TYPE [ schema_name. ] type_name AS TABLE ( { <column_definition> [ , ...n ]})

Explanation: Here the type_name is the table type name and the column definition is similar to a table column having a name and data type. Using Table Type is a 3-step process.

First, we need to define the User defined Table Type.

Exampleof TABLE TYPE:

Query:

CREATE TYPE dbo.ListOfData AS TABLE (Technos varchar(500) )

Explanation: Second, create a stored procedure with Table Type parameter and use the paramter inside the stored procedure to process data passed to the stored procedure. In our example we are using this inside the IN clause as parameter to fetch multiple values.

Below is an example of how to create a stored procedure with Table Type parameter.

Query:

CREATE PROCEDURE GetTechRecords 
@Techs dbo.ListOfData READONLY
AS
BEGIN

Select * from Employees where Technology IN (Select Technos from @Techs)

END
GO

Explanation: In the stored procedure 'GetTechRecords' @Techs is a parameter of table type ListOfData which we created in the first step. Also, the parameter is used in the IN clause to fetch values from this parameter. as below.

Query:

IN (Select Technos from @Techs)

Third, create a Table Type variable and add values or data records and pass this to the stored procedure as parameter. Below is the example of creating Table Type variable and adding data.

Query:

DECLARE @Techvalues dbo.ListOfData;
INSERT @Techvalues (Technos) VALUES('React Native, Flutter'),('React Native'),('AZURE'),('JAVA'),('ASP.NET');

Now call the stored procedure with the table type parameter.

Query:

EXEC dbo.GetTechRecords @Techs = @Techvalues;

From the above we can understand how data is added to a table type variable and passed a parameter value of the stored procedure.

Output:

Explanation: The output shows records from table with data like 'React Native, Flutter','React Native','AZURE','JAVA','ASP.NET' in Technology column of the table Employees.

Conclusion

In this article we have discussed with examples about how to parameterize IN clause in SQL Server. There are different methods about parameterizing the IN clause and in this article we have seen the3 methods namely using multiple String variables, STRING_SPLIT function with String variable and Table Type variable parameter.

julietmaria

Follow

Improve

Article Tags :

• Geeks Premier League
• SQL Server
• Databases
• Geeks Premier League 2023
• SQL Server Query

+1 More