SQL Server parameterized query: where in and like implementation

As a little programmer, it is inevitable to deal with where in and like in daily development, in most cases, the parameters we pass are not much simple, single quotation marks, sensitive characters escape, and then directly spelled into the SQL, execute the query, done. If one day you inevitably need to improve the SQL query performance, and you need to place hundreds, thousands, or even tens of thousands of pieces of data at a time, parameterized query will be an inevitable choice. However, how to implement the where in and like parameterized queries is a headache for many people.

Implementation of where in parametric Query

First of all, let's talk about the common method to implement SQL directly, which can meet the needs in general.
Copy codeThe Code is as follows:
String userIds = "1, 2, 4 ";
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
Comm. CommandText = string. Format ("select * from Users (nolock) where UserID in ({0})", userIds );
Comm. ExecuteNonQuery ();
}

An error is reported when an SQL statement is executed as follows:
Copy codeThe Code is as follows:
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
Comm. CommandText = "select * from Users (nolock) where UserID in (@ UserID )";
Comm. Parameters. Add (new SqlParameter ("@ UserID", SqlDbType. VarChar,-1) {Value = "1, 2, 4 "});
Comm. ExecuteNonQuery ();
}

Obviously, an error is reported: failed to convert the varchar values '1, 2, 3, and 4' to the Data Type int, because the parameter type is a string, in where in, @ UserID is treated as a string, which is equivalent to executing the following statement:
Copy codeThe Code is as follows:
Select * from Users (nolock) where UserID in ('1, 2, 3, 4 ')

If the executed statement is of the string type, no error is reported during SQL Execution, and no results are displayed.
Copy codeThe Code is as follows:
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
Comm. CommandText = "select * from Users (nolock) where UserName in (@ UserName )";
Comm. Parameters. Add (new SqlParameter ("@ UserName", SqlDbType. VarChar,-1) {Value = "'john', 'dudu', 'rabbit '"});
Comm. ExecuteNonQuery ();
}

In this way, no errors will be carried and expected results cannot be found, because this @ UserName is treated as a string, which is equivalent to executing the following statement.
Copy codeThe Code is as follows:
Select * from Users (nolock) where UserName in ('''john'', ''dudu'', ''rabbit ''')

I believe that you know why simple where in passing parameters cannot get the correct results. Let's take a look at how to implement correct parameterized where in execution, in order to truly implement parametric where in parameter passing, many moles have come up with various alternative solutions.

Solution 1: Use the CHARINDEX or like method to implement parameterized query. There is no doubt that this method is successful and the query plan is reused successfully, however, it also permanently invalidates the query index (this topic is not discussed here), resulting in full table scanning. If the data volume in the table is large, it may be millions, tens of millions, or even more, this writing method will have disastrous consequences. If the data size is small and you only want to use parameterization to prevent SQL injection, it is understandable to write this method. You still have to look at the specific requirements. (Not recommended)

Copy codeThe Code is as follows:
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
// Use CHARINDEX to implement parameterized query. You can reuse the query plan and invalidate the index.
Comm. commandText = "select * from Users (nolock) where CHARINDEX (',' + ltrim (str (UserID) + ',' + @ UserID + ', ')> 0 ";
Comm. Parameters. Add (new SqlParameter ("@ UserID", SqlDbType. VarChar,-1) {Value = "1, 2, 4 "});
Comm. ExecuteNonQuery ();
}

Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
// Use like to implement parameterized query. You can reuse the query plan and invalidate the index.
Comm. commandText = "select * from Users (nolock) where ',' + @ UserID + ', 'like' %,' + ltrim (str (UserID) + ', % '";
Comm. Parameters. Add (new SqlParameter ("@ UserID", SqlDbType. VarChar,-1) {Value = "1, 2, 4 "});
Comm. ExecuteNonQuery ();
}

Solution 2 uses exec to dynamically Execute SQL statements. This method is undoubtedly very successful, and the code is also elegant. It also plays a role in preventing SQL injection and looks perfect, however, there is no substantial difference between this writing method and direct SQL Execution. The query plan is not reused, and it does not help in performance improvement. It is quite a bit of a feeling of getting rid of your pants, but it is also a solution. (Not recommended)
Copy codeThe Code is as follows:
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
// Execute SQL dynamically using exec
// The actual query plan is (@ UserID varchar (max) select * from Users (nolock) where UserID in)
// Unexpected (@ UserID varchar (max) exec ('select * from Users (nolock) where UserID in ('+ @ UserID + ')')
Comm. CommandText = "exec ('select * from Users (nolock) where UserID in ('+ @ UserID + ')')";
Comm. Parameters. Add (new SqlParameter ("@ UserID", SqlDbType. VarChar,-1) {Value = "1, 2, 4 "});
Comm. ExecuteNonQuery ();
}

Solution 3 generates a parameter for each parameter of where in, which is more difficult to write. The number of transmitted parameters is limited. A maximum of 2100 parameters can be used as needed (recommended)
Copy codeThe Code is as follows:
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
// Add a parameter for each piece of data
Comm. CommandText = "select * from Users (nolock) where UserID in (@ UserID1, @ UserId2, @ UserID3, @ UserID4 )";
Comm. Parameters. AddRange (
New SqlParameter [] {
New SqlParameter ("@ UserID1", SqlDbType. Int) {Value = 1 },
New SqlParameter ("@ UserID2", SqlDbType. Int) {Value = 2 },
New SqlParameter ("@ UserID3", SqlDbType. Int) {Value = 3 },
New SqlParameter ("@ UserID4", SqlDbType. Int) {Value = 4}
});

Comm. ExecuteNonQuery ();
}

Solution 4: Use a temporary table (or use table variables for better performance, you can write a common where in temporary table query method as needed to meet your needs. I personally recommend this method to reuse the query plan and make effective use of the index, however, temporary tables need to be created, resulting in additional I/O overhead. If the query frequency is high, solution 3 is recommended if there is not much data each time, we strongly recommend that you use this solution, especially for thousands or even tens of thousands of records, to greatly improve the performance (strongly recommended)
Copy codeThe Code is as follows:
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
String SQL = @"
Declare @ Temp_Variable varchar (max)
Create table # Temp_Table (Item varchar (max ))
While (LEN (@ Temp_Array)> 0)
Begin
If (CHARINDEX (',', @ Temp_Array) = 0)
Begin
Set @ Temp_Variable = @ Temp_Array
Set @ Temp_Array =''
End
Else
Begin
Set @ Temp_Variable = LEFT (@ Temp_Array, CHARINDEX (',', @ Temp_Array)-1)
Set @ Temp_Array = RIGHT (@ Temp_Array, LEN (@ Temp_Array)-LEN (@ Temp_Variable)-1)
End
Insert into # Temp_Table (Item) values (@ Temp_Variable)
End
Select * from Users (nolock) where exists (select 1 from # Temp_Table (nolock) where # Temp_Table.Item = Users. UserID)
Drop table # Temp_Table ";
Comm. CommandText = SQL;
Comm. Parameters. Add (new SqlParameter ("@ Temp_Array", SqlDbType. VarChar,-1) {Value = "1, 2, 4 "});
Comm. ExecuteNonQuery ();
}

Like parameterized Query
Like query writes wildcards to parameter values based on your habits or concatenates SQL statements. The two methods have the same effect and are not described here.
Copy codeThe Code is as follows:
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
// Write % to the parameter value
Comm. CommandText = "select * from Users (nolock) where UserName like @ UserName ";
Comm. Parameters. Add (new SqlParameter ("@ UserName", SqlDbType. VarChar, 200) {Value = "rabbit % "});
Comm. ExecuteNonQuery ();
}

Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
// Concatenate % in SQL
Comm. CommandText = "select * from Users (nolock) where UserName like @ UserName + '% '";
Comm. Parameters. Add (new SqlParameter ("@ UserName", SqlDbType. VarChar, 200) {Value = "rabbit % "});
Comm. ExecuteNonQuery ();
}

The comments of Tom. Tang and mosquitoes added the xml parameter passing and tvp parameter passing, and made a summary of the six methods.

SQL Server parameterized query-where in and like implementation-passing parameters through xml and DataTable

This article is an original Lazy fat rabbit