Script base
1. USE statement
Setting up the current database
2. Declare variables
Syntax :DECLARE @ variable name Variable example
After declaring a variable and before assigning a value to it, the value of the variable is NULL.
Assigning system functions to declared variables makes it safer to take advantage of values that change only if they are artificially changed. If we use the system function itself directly, then when it changes, we have
I can’t be sure why, after all, because most of the values of the system functions are determined by the system. This simply causes the system to change its value when you don’t expect it to, with unexpected consequences.
(1). Assign values to variables
SET: Uses SET when a variable assignment is performed and the value is already known to be relevant to presumably other variables.
SELECT: Use SELECT when variable assignment is based on a query.
(2). System function
There are more than 30 system functions with no parameters in SQL Server 2005. Some of the most important are the following:
If you have a problem with C/C++ one item is a very enthusiastic one (● ‘◡’ ●).
@@ERROR: Returns the ERROR code of the last T-SQL statement executed under the current connection. If there is no ERROR, 0 is returned.
@@fetch_STATUS: works with the FETCH statement.
@@identity: Returns the automatically generated IDENTITY value of the last run statement as a consequence 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 events, especially the bottleneck level for the current link events.
Batch processing
(1).go is in a separate line. A T-SQL statement cannot precede a GO statement on the same line.
(2). All statements are compiled from the start of the script about the last GO statement until the script is finished about the next GO statement. This code is compiled into a implementation plan and sent to the server independently of each other. The previous
Errors in one execution plan will 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 sees GO as a finished batch marker, packages it, and sends it as a separate unit
Server – Does not include GO, servers have no concept of GO.
1. Errors in batch processing
Syntax error, runtime error.
2. When to take advantage of batch processing
(1). Separate batch processing statements
Several orders must be processed separately in batches. They include:
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TRIGGER
CREATE VIEW
If you want any of these statements to form a separate script from the others, you need to take a GO statement to separate them into their respective batches.
(2). Establish priority by batch processing
The strongest example of using batch processing is when requirements are concerned with prioritizing statement execution, that is, requiring one task to be performed before another begins.
For example:
CREATE DATABASE Test
Copy the following code:
CREATE TABLE TestTable
(
col1 INT,
col2 INT
)
Execute the statement and find that the generated table is not in the Test database, but in the master database (if the current database is the system database). Because of the data utilized at the time of execution of the script
The library is the system database, which is current, so the generated tables are in the system database. It seems that database Test should be specified before the table is set up. However, there are still problems with this. The parser attempts to calibrate
Checking the code, we find that the database referenced with the USE command does not exist. The reason for this is that the statements to set up the database and the statements to set up the table are written in the same batch before executing the script, which of course the database does not
In accordance with the requirements of batch processing, we will set up the database and set up the table script into two independent batch using GO statement, the exact code is as follows:
Copy the following code:
CREATE DATABASE Test
GO
USE Test
CREATE TABLE TestTable
(
col1 INT,
col2 INT
)
Dynamic SQL: use EXE command to generate code
Grammar: EXEC/EXECUTE ({< string variable > | ‘< literal words string >})
1. Scope of EXEC
The line that actually calls the EXEC statement has the same scope as other code in the batch or process that the EXEC statement is running. But code that is executed as a consequence of an EXEC statement is considered to be executed in its own right
In their own batch.
For example:
DECLARE @OutVar VARCHAR(50)
EXEC (‘SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1’)
Because EXEC’s statement is a separate batch, variables in this batch cannot communicate with outside scopes and are only valid in this batch. this
If @outvar is NULL, then @outvar is NULL.
EXEC (‘DECLARE @OutVar VARCHAR(50)
SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1′)
Here, we see two different scopes that cannot communicate with each other. Without an external mechanism, such as a temporary table, there is no way to implement the current internal and external scopes
One exception is that it can be rendered inside EXEC’s area and seen after EXEC executes: system functions. So, variables like @@rowCount are still
Can be used.
2. Security context and EXEC
When someone is given the right to run a stored process, it means that he is also given the right to perform actions within the stored process. For example, there is a store history that lists all employees hired in the last year. Permissions are available here
Only the person who performs this storage process can perform it and return the consequences — even if he does not have the authority to directly answer hr’s employee table.
Thus the implied permissions on the EXEC statement are invalid. Any reference established within an EXEC statement will run in the security context of the current user, with tacit consent. Therefore, we have the right to visit
Ask a storage history called spNewEmployee, but have no right to wait on the employee table. If spNewEmployee gets the value from a simple SELECT statement, everything is fine. But if
PNewEmployee uses the EXEC statement to execute a SELECT statement. The EXEC statement will fail because it has no right to wait on the employee table.
3. Associate user-defined functions with EXEC
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 CAST needs to be parsed before EXEC’s line. The exact 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 input value of EXEC is already a good string.
EXEC and user-defined functions
In general, user-defined functions are not allowed to run dynamic SQL using EXEC internally, but in rare cases it is reasonable to run a storage process using EXEC.