SQL Server Series: Using Transact-SQL programming

1. Variables

T-SQL variables are declared by the declare command. During declaration, you must specify the variable name to be declared and its data type after the declare command. The data types that can be used include all the data types in the create table command, and the table and sqlvariant data types. When declaring multiple variables in a single declare command, use commas to separate them.

1.1 default values and scopes of Variables

Scope of the variable (that is, the application where the variable can be used)ProgramAnd variable lifecycle) can only be the current batch processing. The default value of the newly declared variables is null. before using them in an expression, you must assign them an initial value.

The following steps create two Test variables and show their initial values and scopes. It consists of two batches (separated by go), but they belong to the same execution process.

 Declare   @ Test   Int  ,  @ Testtwo   Nvarchar ( 25  )  Select  @ Test , @ Testtwo  Set   @ Test   =   1  Set   @ Testtwo   =   '  A Value  '  Select   @ Test , @ Testtwo  Go  Select  @ Test , @ Testtwo 

The execution result is as follows:

The first select returns two null values. After assigning values to the two variables, the second select correctly displays the new values of the two variables. After the batch processing is completed (because of the batch processing end sign go), the lifecycle of all variables ends. Therefore, the last SELECT statement returns the error message No. 137.

1.2 use the set and select commands.

Both the set and select commands can assign values to variables using expressions. The main difference between them is that select can retrieve data from tables, subqueries, or views, and can also contain other select statements. The set command can only retrieve data from expressions. Functions can be used in the set and select commands.

The SELECT statement can retrieve multiple columns, and the data in each column can be assigned to a variable. If the SELECT statement returns multiple rows, the data in the last row of the result set is assigned to the variable, and no error is reported.

 Declare   @ Tempid   Int  ,  @ Tempname   Nvarchar ( 25  )  Set   @ Tempid   =   100  Select   @ Tempid   =  Personid,  @ Tempname   =  Persionname  From  Persion  Select  @ Tempid , @ Tempname 

If the SELECT statement does not return any rows, it will not change the value of the variable.

1.3 conditional select

Because select statements can contain where clauses, you can use the following syntax to assign values to variables:

 
Select @ Variable =ExpressionWhereBooleanexpression
 

The where clause serves the same purpose as the conditional if statement. If the Boolean expression is true, select is executed. If it is false, the SELECT statement will not be executed; because the SELECT statement is not executed, the value of @ variable will not be changed.

1.4 Use variables in SQL queries

Declare @ Productno Char(10)Set @ Productno = '10000'SelectProductnameFromProductWhereProductno= @ Productno
 

2. Process Control

2.1. If

Only one if command can be controlled.

 
IfCondition Statement
 

2.2. Begin/end

An if command can only control the execution of one statement, which is not practical. To solve this problem, you can use the begin/end block, which can take multiple commands as a whole to form the next command of the IF command.

 
IfConditionBeginMultiple linesEnd
 

1>, if exists ()

The IF exists () structure is determined based on whether the result set returned by the SQL SELECT command contains any. Because it only needs to check whether there are rows in the result set, all columns (select *) should be retrieved in the SELECT statement compiled for it *). This method is faster than checking the @ rowcount> 0 condition, because it does not need to know the total number of rows in the result set during judgment, you only need to determine whether there is a row in the result set. In if exists (), if a query returns a record, you can stop the query and execute other statements in the batch processing.

  If   exists  ( select   *   from   [  order  ]   where   [  orderid  ]  =  100  )   begin   Print   '  process orders   '  end  

Compared to the exists () function that only requires select to select the primary key, the Select * method is better. This is due to two reasons. First, the * method is faster, in this case, SQL Server can select the fastest index for query. Second, depending on the different service pack levels of SQL Server, for tables that use guid as the primary key, using select to select the primary key may fail.

2>. If/else

The else command is optional. The statement placed after the else command is executed only when the if condition returns false results.

3. Temporary tables and table Variables

3.1 local temporary table

The method for creating a temporary table is basically the same as that for creating a user-defined table, but the table name starting with # must be used when creating a temporary table. The life cycle of a temporary table is short. When the batch processing or process for creating the temporary table is completed, the temporary table is deleted.

 
Create Table# Producttemp (productidInt Primary Key)
 

3.2 global temporary table

All users can reference the global temporary table. It is deleted only after the last session that references it. To create a global temporary table, you must use the name (# tablename) Starting with two ## tables ).

3.3. Table Variables

A table variable is similar to a temporary table. It only exists in memory. Table variables have the same scope and lifecycle as variables. They can be viewed only by the batch processing, process, or function that creates them. When the batch processing, process, or function ends, the corresponding table variables no longer exist.

 Declare   @ Worktable   Table  (ID  Int   Primary   Key  , Name  Varchar ( 50  ))  Insert   Into   @ Worktable (ID, name)Values ( 1 , '  1000  '  )  Select ID, name From   @ Worktable 

3.4 dynamic SQL

Dynamic SQL is particularly suitable for the following tasks:

Using multiple query conditions to create custom where statements

Create a custom from clause based on the where clause to include only the required tables and connections.

Users dynamically create different order by statements based on user requirements and sort data in different ways

1> execute dynamic SQL

Exec[Ute](T-SQL Batch)WithRecompile
 

Use the with recompile option to force SQL Server to recompile a specified T-SQL batch, instead of using a previously executed query execution plan. If the T-SQL string and parameters change significantly, using the with recompile option prevents SQL Server from using a query plan that does not match it, resulting in performance degradation. But if the executed T-SQL strings are all similar statements, you do not need to re-compile with recompile option, which reduces performance. Because most dynamic SQL processes create extremely different SQL statements, it is appropriate to use the with recompile option in general.

 
Exec('Select * from product where productid = 10')
 

2> sp_executesql

ExecSp_executesql'T-SQL'Query parameters definition parameter, parameter ,...
 

The SQL statements and parameter definitions for the T-SQL must use Unicode strings.

You can use parameters to optimize the performance. If you use the same parameter each time you only need to T-SQL the statement, you should use sp_executesql and the corresponding parameter to execute it, so that you can save the query plan, the performance will be optimized when the statement is executed in the future.

ExecSp_executesql n'Select productname from product where productid = @ productid', N'@ Productid int',@ Productid=10