SQL server script and batch processing command Summary

I. Script Basics
1. USE statement
Set the current database.
2. Declare Variables
Syntax: DECLARE @ variable name variable type
After a variable is declared, the value of the variable is NULL before the value is assigned.
Assign the system function to the declared variable. This method enables us to use the value more securely. The value changes only when it is artificially changed. If you directly use the system function itself, when it changes
You cannot determine what it is, because most system function values are determined by the system. This can easily cause unexpected system changes and unexpected consequences.
(1) assign values to variables
SET: When you assign a value to a variable that is known as the exact value or another variable, use SET.
SELECT: when the value of a variable is based on a query, SELECT is used.
(2). system functions
SQL Server 2005 has more than 30 system functions without parameters, some of the most important of which are as follows:
@ ERROR: return the ERROR code for the last executed T-SQL statement under the current connection, if no ERROR is returned 0.
@ FETCH_STATUS: used with the FETCH statement.
@ IDENTITY: return the automatically generated id value of the last running statement as the result of the last INSERT or select into statement.
@ ROWCOUNT: returns the number of rows affected by the last statement.
@ SERVERNAME: return the name of the local service on which the script is running.
@ TRANCOUNT: returns the number of active transactions, especially the transaction bottleneck for the current connection.

Ii. Batch Processing
(1). GO occupies a single row. On the same line, the T-SQL statement cannot be before the GO statement.
(2 ). all statements are compiled from the beginning of the script or the previous GO statement until the next GO statement or script ends. The code is compiled into an execution plan and sent to the server independently. Previous
An error occurs in an execution plan, which does not affect the next execution plan.
(3). GO is not a T-SQL command, but a command recognized by the editing tool. When the editing tool encounters GO, it regards GO as a tag for ending batch processing, packs it, and sends it as an independent unit
Server -- does not include GO. The server has no concept of GO.
1. batch processing errors
Syntax error, runtime error.
2. When to use Batch Processing
(1). Statements processed in batches independently
Several commands must be processed in batches independently, including:
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TRIGGER
CREATE VIEW
If you want to separate one of these statements and other statements into a single script, you need to use a GO statement to separate them and classify them into their respective batches.
(2). Use batch processing to establish priority
The most reliable example of batch processing is that when statement execution is prioritized, that is, one task needs to be executed before another task starts.
For example:
Create database Test
Copy codeThe Code is as follows:
Create table TestTable
(
Col1 INT,
Col2 INT
)

Execute the statement and you will find that the generated table is not in the Test database, but in the master database (if the current database is a system database ). Because the data used during Script Execution
The database is a system database, which is the current database, so the generated tables are in the system database. It seems that the database Test should be specified before the table is created. However, there are still problems. Parser trying to school
Check the code and find that the database referenced by the USE command does not exist. The reason is that the statement for creating a database and the statement for creating a table are written in a batch. Before the script is executed, the database does not
Create. Based on the requirements of batch processing, we divide the script for creating a database and creating a table into two separate batches using the GO statement. 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
)

Iii. dynamic SQL: Use the EXE command to generate code
Syntax: EXEC/EXECUTE ({<string variable >|' <nominal value command string> '})
1. EXEC Scope
The row that calls the EXEC statement has the same scope as the code in the batch or process where the EXEC statement is running. However, the Code executed as the result of the EXEC statement is considered
Your batch is in progress.
For example:
DECLARE @ OutVar VARCHAR (50)
EXEC ('select @ OutVar = FirstName FROM Contact WHERE ContactID = 1 ')
The system reports an error, indicating that the variable @ OutVar must be declared. Because the EXEC statement becomes a batch processing independently, the variables in the batch processing cannot communicate with the external scopes, but are valid only in this batch processing. This
@ OutVar is NULL. The correct syntax is as follows:
EXEC ('Clare @ OutVar VARCHAR (50)
SELECT @ OutVar = FirstName FROM Contact WHERE ContactID = 1 ')
Here, we can see two different scopes, which cannot communicate with each other. Without external mechanisms, such as a temporary table, we cannot implement internal and external scopes.
Information. One exception is that it can appear inside the EXEC region and be seen after EXEC is executed. This is a system function. Therefore, variables such as @ ROWCOUNT are still
Can be used.
2. Security Context and EXEC
When giving a person the right to run a stored procedure, it means that he can also get the right to execute actions inside the stored procedure. For example, a stored procedure is used to list all the employees hired last year. Permission
The person who executes the stored procedure can execute and return results, even if he does not have the permission to directly access the HR employee table.
In this way, the implicit permission is invalid for the EXEC statement. By default, any reference created within an EXEC statement will run in the security context of the current user. Therefore, we have the right to access
A stored procedure called spNewEmployee has no right to access the employee table. If spNewEmployee obtains the value through a simple SELECT statement, everything works. However, if
PNewEmployee uses the EXEC statement to execute a SELECT statement. This EXEC statement will fail because it has no right to access the employee table.
3. Associate user-defined functions with EXEC
You cannot run a function or EXEC statement in the same statement at the same time. 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. 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 normally because the EXEC input value is already a complete string.
4. EXEC and user-defined functions
Generally, user-defined functions are not allowed to run dynamic SQL using EXEC. However, it is legal to use EXEC to run a stored procedure.