T-SQL Script

　　1. Use statement

The USE statement is used to set the current database, and if there is no using statement, then any user executing the script determines that the current database is correct when the script is executed. If it's just a generic script, it might actually be more beneficial to omit the use statement. In general, if you name a database-specific table (not a system table) in your script, you need the use command. If the script is used to modify a particular database, you can see that this is very helpful. Otherwise, the default script is executed against master.

 Use Master

2. Declaring variables

The syntax of the DECLARE statement is fairly straightforward:

    DECLARE @<variable name><variable type>[= <value>][,    @<variable name> <variable type> [= <value>] [  ,    @<variable name> <variable type> [= <value>]]

You can declare only one variable at a time, or you can declare several variables at once. Instead of declaring multiple variables at once with a comma-delimited method, people often reuse declare statements in a way that declares one variable at a time. Regardless of the method you choose, you must initialize the variable (using the "=" syntax), otherwise the value of the variable is null until it is set to some other value in the display.

DECLARE @i int,@jint

3. Set the value in the variable

　　3.1 Setting variables using Set

There are two ways to set the value of a variable. You can use the Selece statement or the SET statement. Functionally, they are almost identical, unlike the SELECT statement, which allows metadata values to come from a column in a SELECT statement.

DECLARE @i int,@jintset@i=ten; Set @j =  - ; Select @i + @j

To set a variable with a queried value

DECLARE @i int SET @i = (SelectMAX from person )--Note This line code, if you remove the outside parenthesis error SELECT @i

　　3.2 Setting a variable with select

When the information stored in a variable originates from a query, it is often used to assign a value to a variable using SELECT, which is easier to syntax.

DECLARE @i int SELECT @i =  - SELECT @i

The information for the query is assigned to the variable:

DECLARE @i int SELECT @i = MAX  from  Person SELECT @i

To set the selection of variables:

• When you perform a simple variable assignment, using set-the known value is an explicit value or other variable.
• Use Select when variable assignment is based on a query.

　　

T-SQL Script