I. Scripting Basics
1.USE statement
Sets the current database.
2. Declaring variables
Syntax: DECLARE @ variable name variable type
After you declare a variable, the value of the variable is null before assigning the variable.
Assigning a system function to a declared variable allows us to use the value more securely, which changes only when it is artificially changed. If the system function itself is used directly, then when it changes, there are
It is not possible to determine exactly why, because most system function values are determined by the system. This can easily cause the system to change its value without expectation, causing unpredictable consequences.
(1). Assigning values to variables
Set: Sets are used when the variable is assigned a value that is already known to be an exact value or another variable.
Select: Use Select when the variable assignment is based on a query.
(2). system function
There are more than 30 parameterless system functions in SQL Server 2005, some of which are 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 run 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 that the script is running on.
@ @TRANCOUNT: Returns the number of active transactions, especially the bottleneck for current connected transactions.
two. Batch processing
(1). Go on a separate line. On the same line, T-SQL statements 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 each other to the server independently. Before a
An error occurs in an execution plan that does not affect the latter execution plan.
(3). Go is not a T-SQL command, but a command recognized by the editing tool. When the editing tool hits go, it sees the go as a tag to end the batch, packs it, and sends it as a standalone unit to
Server--does not include go, the server has no concept for go.
1. Errors in batch processing
Syntax error, run-time error.
2. When to use batch processing
(1). Statements that are handled in batches alone
Several commands must be handled by themselves in batches, including:
CREATE DEFAULT
CREATE PROCEDURE
CREATE rule
CREATE TRIGGER
CREATE VIEW
If you want to make any of these statements and other statements a separate script, then you need to use a go statement to separate them from each other in their batch.
(2). Use batch processing to establish priority
The most reliable example of using batching is when you need to consider the precedence of statement execution, which means that you need a task to execute before another task starts.
For example:
CREATE DATABASE Test
Copy Code code as follows:
CREATE TABLE TestTable
(
Col1 INT,
Col2 INT
)
Executing the statement, you will find 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 when the script is executed, the data used
The library is a system database, the database is current, so the resulting table is in the system database. It seems that database test should be specified before the table is created. However, this still has a problem. Parser tries to school
The code found that the database we referenced with the use command does not exist. The reason is that the statements that create the database and the statements that create the table are written in a batch, before the script is executed, of course, the database has not
Create. According to the requirements of batch processing, we divide the scripts that create the database and create tables into two separate batches using the GO statement. The correct code is as follows:
Copy Code code as follows:
CREATE DATABASE Test
Go
Use Test
CREATE TABLE TestTable
(
Col1 INT,
Col2 INT
)
three. Dynamic sql: Using EXE command to generate code
Syntax: Exec/execute ({< string variable > | ' < literal command string > '}
Scope of the 1.EXEC
The actual line that invokes the EXEC statement has the same scope as the other code in the batch or procedure that the EXEC statement is running. But the code executed as the result of the EXEC statement is considered to be in its own
In your own batch.
For example:
DECLARE @OutVar VARCHAR (50)
EXEC (' SELECT @OutVar = FirstName from contacts WHERE ContactID = 1 ')
Here the system will report an error, indicating that the variable @outvar must be declared. Because the EXEC statement becomes a batch alone, the variables cannot be communicated with the scope of the outside, only valid in this batch. This
, the value of the @OutVar is null. The correct wording is as follows:
EXEC (' DECLARE @OutVar VARCHAR (50)
SELECT @OutVar = FirstName from contacts WHERE ContactID = 1 ')
Here, we see two different scopes where the two scopes cannot communicate with each other. Without an external mechanism, such as a temporary table, we would have no way of implementing both the internal scope and the external scope
Pass information between. One exception to this is that it can be seen within the exec area and can also be viewed after exec execution, which is the system function. Therefore, 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 get the right to perform the actions inside the stored procedure. For example, there is a stored procedure that lists all the employees hired last year. which has permission
The person executing the stored procedure can execute and return the result-even if he does not have permission to access the employee table of the human resources directly.
This implied permission is not valid for the EXEC statement. By default, any references that are established within an EXEC statement are run in the security context of the current user. Therefore, we have the right to visit
Ask a stored procedure called Spnewemployee, but there is no right to access the employee table. If Spnewemployee gets the value through a simple SELECT statement, then everything is fine. But if
Pnewemployee uses the EXEC statement to execute a SELECT statement that 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 the 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 contacts ')
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 contacts '
EXEC (@str)
This example works fine, because exec's input value is already a complete string.
4.EXEC and user-defined functions
In general, user-defined functions are not allowed to run dynamic SQL internally using Exec, but in a few cases it is legal to run a stored procedure with exec.
