SQL Server parameterized query: Where in and like implementation

Address: http://www.cnblogs.com/lzrabbit/archive/2012/04/22/2465313.html

 

 ArticleGuide

SQL query for where in

Use charindex or like to implement where in parameterization

Use exec to dynamically Execute SQL to implement where in parameterization

Generate a parameter for each parameter to implement where in parameterization

Use a temporary table to implement where in parameterization

Like parameterized Query

Passing parameters in XML and datatable

As a smallProgramApe, we cannot avoid dealing 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.

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 * FromUsers (nolock)WhereUseridIn({0}) ", Userids); Comm. executenonquery ();}
 

An error is reported when an SQL statement is executed as follows:

Using (sqlconnection Conn=New sqlconnection (connectionstring) {Conn.Open(); Sqlcommand comm=New sqlcommand (); Comm. Connection=Conn; Comm. commandtext="Select * FromUsers (nolock)WhereUseridIn(@ Userid) "; Comm. parameters.Add(New sqlparameter ("@ Userid", Sqldbtype.Varchar,-1) {Value="1,2,3,4"}); Comm. executenonquery ();}
 

Obviously, an error will be reported:Failed to convert varchar values '1, 2, 3, 4 'to data type intBecause the parameter type is string, @ userid is treated as a string in where in, which is equivalent to the actual execution of the following statement

Select * FromUsers (nolock)WhereUseridIn('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.

Using (sqlconnection Conn=New sqlconnection (connectionstring) {Conn.Open(); Sqlcommand comm=New sqlcommand (); Comm. Connection=Conn; Comm. commandtext="Select * FromUsers (nolock)WhereUsernameIn(@ 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.

Select * FromUsers (nolock)WhereUsernameIn('''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 QueryThere is no doubt that this method has succeeded, and the query plan has been reused successfully, but it also permanently invalidates the query index (I will not discuss the index topic here ), the consequence is full table scan. If the data volume in the table is large, millions, tens of millions, or even more, such writing will cause disastrous consequences; if the data volume is small and you only want to use parameterization to prevent SQL injection, it is understandable to write the statement as needed. (Not recommended)

Using (sqlconnection Conn = New sqlconnection (connectionstring) {Conn. Open (); Sqlcommand comm = New sqlcommand (); Comm. Connection = Conn; // Charindex is used 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 , 3 , 4 "}); Comm. executenonquery ();} using (sqlconnection Conn = New sqlconnection (connectionstring) {Conn. Open (); Sqlcommand comm = New sqlcommand (); Comm. Connection= Conn; // Using like to implement parameterized query, You can reuse the query plan, and make the index invalid comm. commandtext = " Select   *   From Users (nolock) Where   '  ,  '  +  @ Userid  +  '  ,  '   Like    '  %,  '  +  Ltrim ( Str (Userid )) +  '  , %  ' "; Comm. parameters. Add (New sqlparameter (" @ Userid ", Sqldbtype. Varchar , -  1 ) {Value = " 1 , 2 , 3 , 4 "}); Comm. executenonquery ();}

 Solution 2 Execute SQL dynamically using ExecThis method is undoubtedly very successful, andCodeIt is also relatively elegant, and has also played a role in preventing SQL injection. It looks perfect, but there is no substantial difference between this writing method and direct SQL Execution, and the query plan is not reused, it is not helpful for performance improvement. It is quite a way of getting rid of your pants and farting, but it is 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 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 , 3 , 4 "}); Comm. executenonquery ();}

Solution 3 generates a parameter for each parameter of where inIt is more difficult to write, and the number of transmitted parameters is limited. A maximum of 2100 parameters can be used as needed (recommended)

Using (sqlconnection Conn = New sqlconnection (connectionstring) {Conn. Open (); Sqlcommand comm = New sqlcommand (); Comm. Connection = Conn; // Add the comm. commandtext parameter to each piece of data. = " 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(You can also use table variables for better performance.). You can write a common where in temporary table query method based on your needs, the query plan can be reused and the index can be effectively used. However, creating a temporary table may lead to additional Io overhead. If the query frequency is high, solution 3 is recommended for a small amount of data each time. If the number of data entries is large, especially when thousands or even tens of thousands of entries are queried, this solution is strongly recommended, it can bring huge performance improvements (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_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, 3, 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.

 Using (Sqlconnection conn = New Sqlconnection (connectionstring) {conn. open (); sqlcommand comm = New Sqlcommand (); Comm. Connection = conn; //  Write % to 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; //  Splicing 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