SQL Server script and batch instructions summary

I. Scripting Basics
1.USE statements
Sets the current database.
2. Declaring variables
Syntax: DECLARE @ variable name variable type
After a variable is declared, the value of the variable is null before assigning a value to the variable.
Assigning a system function to a declared variable allows us to use the value more safely, which changes only when the value is artificially changed. If the system function itself is used directly, then when it changes, there is
It is not possible to determine why, because most system function values are determined by the system. It's easy to cause the system to change the value if you don't anticipate it, causing unpredictable consequences.
(1). Assigning values to variables
Set: When the variable assignment is made, the value is already known to be the exact value or another variable, use set.
Select: Use Select when the variable assignment is based on a query.
(2). System functions
There are more than 30 non-parametric system functions in SQL Server 2005, some of which are the most important:
@ @ERROR: Returns the error code for the last T-SQL statement executed under the current connection, if no error returns 0.
@ @FETCH_STATUS: Used in conjunction with the FETCH statement.
@ @IDENTITY: Returns the automatically generated identity value of the last sentence running the statement as the result of the last insert or SELECT INTO statement.
@ @ROWCOUNT: Returns the number of rows affected by the last statement.
@ @SERVERNAME: Returns the name of the local service on which the script is running.
@ @TRANCOUNT: Returns the number of active transactions, especially the bottleneck for transactions that are currently connected.

two. Batch processing
(1). Go is a separate line. On the same line, the T-SQL statement cannot precede the GO statement.
(2). All statements are compiled from the beginning of the script or from the previous GO statement until the next GO statement or script finishes, compiling the code into an execution plan and sending it to the server independently of each other. Previous one
An error in the execution plan does not affect the latter execution plan.
(3). Go is not a T-SQL command, just a command that is recognized by the editing tool. When the editing tool touches go, it sees go as an end-of-batch tag, packages it, and sends it as a standalone unit to
Server--not including go, the server has no concept of go.
1. Errors in batch processing
Syntax error, run-time error.
2. When to use batch processing
(1). batch-alone statements
There are several commands that must be batched on their own, including:
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TRIGGER
CREATE VIEW
If you want to make a separate script for any of these statements and other statements, you need a go statement that breaks them separately into the respective batches.
(2). Using batch processing to establish priority
The most reliable example of using batch processing is when you need to consider the precedence of statement execution, that is, one task needs to be executed before the start of another task.
For example:
CREATE DATABASE Test

Copy CodeThe code is as follows:
CREATE TABLE TestTable
(
Col1 INT,
Col2 INT
)

Executes the statement and finds that the resulting table is not in the test database, but in the master database (if the database currently in use is a system database). Because the data that is used when the script is executed
A library is a system database, and the database is current, so the resulting table is in the system database. It appears that you should specify the database test before creating the table. However, this still has a problem. Parser tries to school
Code, and found that the database we referenced with the use command does not exist. The reason is that the statement that created the database and the statement that created the table were written in a batch, and before the script was executed, of course the database was not
Create. Based on the requirements of the batch processing, we will create the database and create the table script with the GO statement divided into two separate batches. The correct code is as follows:

Copy CodeThe code is as follows:
CREATE DATABASE Test
GO

Use Test
CREATE TABLE TestTable
(
Col1 INT,
Col2 INT
)

three. Dynamic sql: Using the EXE command to generate code
syntax: Exec/execute ({< string variable > | ' < literal command string > '}
Scope of 1.EXEC
The actual row that invokes the EXEC statement has the same scope as the EXEC statement that is running the batch or other code in the procedure. But the code executed as the result of the EXEC statement is considered to be in its own
in their own batches.
For example:
DECLARE @OutVar VARCHAR (a)
EXEC (' SELECT @OutVar = FirstName from contact WHERE ContactID = 1 ')
The system will report an error, stating that the variable @outvar must be declared. Because the EXEC statement is a batch, the variables cannot communicate with the outside scope, only valid in this batch. This
, the value of @OutVar is null. The correct wording is as follows:
EXEC (' DECLARE @OutVar VARCHAR ()
SELECT @OutVar = FirstName from contact WHERE ContactID = 1 ')
here, we see two different scopes in which the two scopes cannot communicate with each other. Without an external mechanism, such as a temporary table, there is no way to implement the internal scope and the outer scope
passing information between them. One of the exceptions is that it can appear inside the exec area and can be seen after exec execution, which is the system function. So variables like @ @ROWCOUNT are still
can be used.
2. Security context and Exec
when someone is given the right to run a stored procedure, it means that he can also gain the right to perform the actions inside the stored procedure. For example, there is a stored procedure that lists all the employees that were hired in the last year. which has permissions
The person executing the stored procedure is able to execute and return the result-even if he does not have access to the employee table of the human resources directly.
this implies that the implicit permission is not valid for the EXEC statement. By default, any references that are established inside an EXEC statement will run in the security context of the current user. Therefore, we have the right to visit
ask a stored procedure called Spnewemployee, but have no right to access the employee table. If Spnewemployee gets the value through a simple SELECT statement, everything is fine. But if
Pnewemployee uses the EXEC statement to execute a SELECT statement, the EXEC statement fails-because there is no right to access the employee table.
3. User-defined functions and exec associations
You cannot run both a function and an EXEC statement in the same statement. For example:
DECLARE @Num INT
SET @Num = 3
EXEC (' SELECT left (LastName, ' + CAST (@Num as VARCHAR) + ') as Filingname from contact ')
This statement returns an error message because the cast function needs to be parsed before the exec row is located. The correct code is as follows:
DECLARE @Num INT
DECLARE @str VARCHAR (255)
SET @Num = 3
SET @str = ' SELECT Left (LastName, ' + CAST (@Num as VARCHAR) + ') as Filingname from contact '
EXEC (@str)
This example works fine because the exec input value is already a complete string.
4.EXEC and user-defined functions
In general, you are not allowed to use exec to run dynamic SQL inside a user-defined function, but using EXEC to run a stored procedure is a rare case of legality.