"Reprint" The Where in and like implementations of SQL Server parameterized queries

Article guide

SQL implementation where in query

Using CHARINDEX or like to implement where in parameterization

Implementing the Where in parameterization using exec dynamic execution of SQL

Generate a parameter for each parameter implement where in parameterization

Using temporal tables to implement where in parameterization

Like parameterized queries

XML and DataTable parameters

As a small program ape, in the daily development can not be avoided to and where in and like to deal with, in most cases we pass the parameter is not much simple to do the single quotation mark, the sensitive character escapes directly into the SQL, execute query, done. If one day you inevitably need to improve SQL query performance, you need a one-time where in hundreds of, thousands, even tens of thousands of data, parameterized query will be the inevitable choice. However, how to implement a parametric query of where in and like is a problem that many people have a headache with.

Parameterized query implementation for where in

First of all, we commonly used methods, directly to the SQL implementation, the general situation can meet the needs

String userids = "1,2,3,4"; using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    Comm.commandtext = string. Format ("SELECT * from the Users (NOLOCK) where UserID in ({0})", userids);    Comm. ExecuteNonQuery ();}

An attempt to make a parameterized query is clearly an error in executing SQL as follows

using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    Comm.commandtext = "SELECT * from the Users (NOLOCK) where UserID in (@UserID)";    Comm. Parameters.Add (New SqlParameter ("@UserID", SqlDbType.VarChar,-1) {Value = "1,2,3,4"});    Comm. ExecuteNonQuery ();}

It is clear that this will report an error: failed when converting the varchar value ' 1,2,3,4 ' to the data type int, because the parameter type is a string and where in will handle the @userid as a string, equivalent to actually executing the following statement

SELECT * from Users (NOLOCK) where UserID in (' 1,2,3,4 ')

If the executed statement is of type string, SQL execution will not give an error, and certainly will not query any results

using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    Comm.commandtext = "SELECT * from the Users (NOLOCK) where UserName in (@UserName)";    Comm. Parameters.Add (New SqlParameter ("@UserName", SqlDbType.VarChar,-1) {Value = "' John ', ' Dudu ', ' Rabbit '"});    Comm. ExecuteNonQuery ();}

This will not hold any errors, and can not find the desired results, because the @username is treated as a string, the actual equivalent of executing the following statement

SELECT * from the Users (NOLOCK) where UserName in (' ' John ', ' Dudu ', ' rabbit ')

It is believed that we can not get the correct result for the simple where in the argument, let's look at how to implement the correct parameterization of where in, and in order to really implement the parameterized where in the argument, a lot of kinky think of a variety of alternatives

Scenario 1, using the charindex or like method to implement parameterized queries , without a doubt, this method succeeds, and successfully reused the query plan, but also completely invalidate the query index (not discussed in the index topic), the result is a full table scan, If the amount of data in the table is large, millions, tens, or even more, such a writing will have disastrous consequences; if the amount of data is small, just want to use parametric implementation to prevent SQL injection, it is understandable, or depends on the specific needs. (Not recommended)

using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    Using CHARINDEX to implement parameterized queries, you can reuse the query plan and invalidate the index    comm. CommandText = "Select * from Users (NOLOCK) where CHARINDEX (', ' +ltrim (str) + ', ', ', ' [email protected]+ ', ') >0";    Comm. Parameters.Add (New SqlParameter ("@UserID", SqlDbType.VarChar,-1) {Value = "1,2,3,4"});    Comm. ExecuteNonQuery ();} using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    Using like to implement parameterized queries, you can reuse the query plan and invalidate the index    comm. CommandText = "Select * from Users (nolock) where ', ' [e-mail protected]+ ', ' like  '%, ' +ltrim (str) + ',% '";    Comm. Parameters.Add (New SqlParameter ("@UserID", SqlDbType.VarChar,-1) {Value = "1,2,3,4"});    Comm. ExecuteNonQuery ();}

Scenario 2 uses exec to execute SQL dynamically, which is undoubtedly a success, and the code is more elegant, it also plays a role in preventing SQL injection, it looks perfect, but this kind of writing and direct SQL execution is not a substantial difference, the query plan is not reused, It's not a good idea to get a performance boost, but it's a way to get off your pants and fart, but it's also a solution. (Not recommended)

using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    Execute SQL dynamically using EXEC
The actual execution of the query plan is (@UserID varchar (max)) SELECT * from Users (NOLOCK) where the UserID in (1,2,3,4)
Not expected (@UserID varchar (max)) EXEC (' SELECT * from Users (NOLOCK) where UserID in (' [email protected]+ ') ')
    Comm.commandtext = "EXEC (' select * from the Users (NOLOCK) where UserID in (' [email protected]+ ') ')";    Comm. Parameters.Add (New SqlParameter ("@UserID", SqlDbType.VarChar,-1) {Value = "1,2,3,4"});    Comm. ExecuteNonQuery ();}

Scenario 3 generates a parameter for each parameter in the where in , which is more cumbersome to use, with a limit on the number of parameters transferred, up to 2,100, and can be used as needed (recommended)

using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    Add a parameter comm for each piece of data    . CommandText = "SELECT * from the 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 ();}
   

Scenario 4 using temporal table implementation ( You can also use table variable performance may be better), the implementation of the more cumbersome, you can write a common where in the temporary table Query method, for a rainy life, personal comparison of this writing, can make the query plan is reused and the index can be effectively used, However, due to the need to create temporary tables, it will bring additional IO overhead, if the query frequency is very high, each time the data is not long or recommended scenario 3, if the number of query data bar, especially thousands or even tens of thousands of bars, it is strongly recommended to use this scenario, can bring a huge performance improvement (highly recommended)

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_Arra        Y,len (@Temp_Array)-len (@Temp_Variable)-1) End insert into #Temp_Table (Item) VALUES (@Temp_Variable) End Select * from the Users (NOLOCK) where exists (select 1 from #Temp_Table (NOLOCK) where #Temp_Table. Item=use Rs.    UserID) drop table #Temp_Table ";    Comm.commandtext = SQL; Comm. Parameters.Add (New SqlParameter ("@Temp_Array ", SqlDbType.VarChar,-1) {Value =" 1,2,3,4 "}); Comm. ExecuteNonQuery ();}

Like parameterized queries
Like query according to personal habits to write wildcard characters in the parameter values or in SQL splicing can, the two methods perform the same effect, not in detail

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, $) {Value = "rabbit%"});    Comm. ExecuteNonQuery ();} using (SqlConnection conn = new SqlConnection (connectionString)) {    Conn. Open ();    SqlCommand comm = new SqlCommand ();    Comm. Connection = conn;    SQL Stitching%    comm.commandtext = "SELECT * from Users (NOLOCK) where UserName like @UserName + '% '";    Comm. Parameters.Add (New SqlParameter ("@UserName", SqlDbType.VarChar, $) {Value = "rabbit%"});    Comm. ExecuteNonQuery ();}

See the comments on Tom's soup and mosquito forehead add the following XML and TVP, and make a brief summary of 6 schemes.

XML and DataTable arguments for the where in and like implementations of SQL Server parameterized queries

"Reprint" The Where in and like implementations of SQL Server parameterized queries