EXECUTE
User-defined functions, system procedures, user define stored procedures or extended stored procedures. Support for the execution of strings within Transact-SQL batch
To wake up call, use the syntax described in Execute Stored_Procedure.
Syntax execution stored procedure:
[[@Return_Status =] {procedure_name [; number] | @procedure_name_var} [[@Parameter =] {value | @variable [output] | [default]] [, ... n] [ With recompile]
Execute a string:
Exec [ute] ({@string_variable | [n] 'tsql_string'} [ ... n])
parameter
@return_status is an optional integer variable that saves the return status of the stored procedure. This variable must be declared in the batch, stored procedure, or function before useful in the Execute statement.
When used to wake up the scheduling value user-defined function, the @ RETURN_STATUS variable can be any scalar data type.
Procedure_name is a fully legal or incompletely legal name that is quarantined by the stored procedure. The process name must meet the identifier rules. For more information, see Using the Identifier. Regardless of the server's code page or sorting method, the name of the extended stored procedure is always case sensitive. The user can execute the process created in another database as long as the user has this process or has the appropriate permissions in the database. The user can execute the process on another server running Microsoft® SQL ServerTM, as long as the user uses the server (remote access), and can execute the process in the database. If the server name is specified but does not specify the database name, SQL Server will find the process in the user's default database.
Number is an optional integer that combines the process of the same name so that they can be removed by a Drop Procedure statement. This parameter cannot be used to expand the stored procedure. The process used in the same application is generally combined in this manner. For example, the process used in ordering applications can be named in ORDERPROC; 1, ORDERPROC; 2, etc.. The Drop Procedure ORDERPROC statement will remove the entire group. After the process is packet, a single process in the group cannot be removed. For example, Drop Procedure ORDERPROC; 2 is not allowed. For more information on the process group, see Create Procedure.
@procedure_name_var is a local definition variable name that represents a stored procedure name.
@Parameter is the process parameters defined in the CREATE Procedure statement. Before the parameter name, you must add a symbol (@). When used in @parameter_name = value format, the parameter name and constant do not necessarily appear in the order defined in the CREATE Procedure statement. However, if there is a parameter using @Parameter_name = value format, all other parameters must use this format. By default, the parameters can be empty. If the NULL parameter value is transmitted, the parameter is not allowed to be NULL in the Create or ALTER TABLE statement (for example, inserting a column that is not allowed to be null), SQL Server will report an error. To avoid passing the NULL parameter value to columns that are not allowed to be NULL, you can add programming logic during the process or use the default value (using the default key in the CREATE or ALTER TABLE statement). Value is the value of the parameters during the process. If the parameter name is not specified, the parameter value must be given in the order defined in the CREATE Procedure statement. If the parameter value is an object name, a string, or through a database name or a owner name, the entire name must be enclosed in single quotes. If the parameter value is a keyword, the keyword must be enclosed in double quotes.
If the default value is defined in the Create Procedure statement, the user can do not have to specify the parameters when the user performs the process. If the process uses a parameter name with a LIKE keyword, the default value must be constant and can contain%, _, [], and [^] wildcard. The default value can also be NULL. Typically, the process definition specifies the operation that should be performed when the parameter value is null.
@variable is used to save parameters or returns a variable of parameters.
Output Specifies that the stored procedure must return a parameter. The matching parameters of this stored procedure must also be created by the keyword Output. Use the cursor variable as a parameter to use this keyword. If you use an OUTPUT parameter, the purpose is to use its return value in other statements that call batch or procedures, the parameter value must be transmitted as a variable (ie @Parameter = @variable). If a parameter is not defined as an OUTPUT parameter in the CREATE Procedure statement, the process of specifying Output for this parameter cannot be executed. You cannot use Output to pass constants to the stored procedure; return parameters require variable names. Before the execution process, the data type of the variable must be declared and assigned. The return parameter can be any data type other than the Text or Image data type.
Default provides the default value of the parameters based on the definition of the process. When the parameter value required by the process does not default, or miss the parameters, or specify the default keyword, it will be wrong.
n is a placeholder, indicating that the project in front of it can be repeated multiple times. For example, an Execute statement can specify one or more @ parameter, value or @variable.
WITH Recompile enforces the new plan. Use this option if the parameters provided are atypical parameters or data. Use the changed plan in the later program execution. This option cannot be used to extend the stored procedure. It is recommended to use this option as little as possible because it consumes more system resources.
@string_variable is the name of the local variable. @string_variable can be a CHAR, VARCHAR, NCHAR, or NVARCHAR data type, and the maximum value is available in the server. If the string length exceeds 4,000 characters, the plurality of local variables are connected in series for the Execute string. For more information on the SQL Server data type provided by the system, see the data type. [N] 'TSQL_String' is a constant, TSQL_String can be NVARCHAR or VARCHAR data type. If N, the string will be interpreted as NVARCHAR data type, the maximum value is available in the server. If the string length exceeds 4,000 characters, the plurality of local variables are connected in series for the Execute string.
Comment
If the first three characters of the process name are SP_, SQL Server will find the process in the Master database. If you have not found a legal process name, SQL Server will look for the process of the owner name DBO. To define stored procedures to define stored procedures with users with the same name as the system stored procedure, provide a completely legal process name.
The parameters can be provided by using Value or @Parameter_name = Value. The parameter is not part of the transaction; thus if the parameter value in the transaction changes, and the transaction rolls back, the parameter value will not return to the previous value. The value returns to the call is always the value of the process returns.
When a stored procedure calls another stored procedure, nested. When the calling process begins, the nested level will increase. When the calling process is completed, the nested level will decrease. The nested level is 32, and when the 32 level will cause the entire calling process chain to fail. The current nested stage is stored in the @@nestlevel function.
SQL Server currently uses return values 0 to -14 to represent the execution status of the stored procedure. The value of -15 to -99 is left after leaving. For more information on the list of returned status values, see Return.
Because the remote stored procedures and extended stored procedures are not in the field of transactions (unless you are using or with different configuration options in the Begin Distributed Transaction statement, you can't roll back by calling the executed command. For more information, see System Store and Begin Distribute Transaction.
When using a cursor variable, it will be wrong if the process of executing a cursor variable is assigned a cursor
When performing a stored procedure, if the statement is the first statement in the batch, do not have to specify the Execute keyword.
Use the execute command with strings
Use string arithmetic ( ) to create a long string for dynamically. Each string expression can be a mixture of Unicode and Non-Unicode data types.
Although each [N] 'tsql_string' or @string_variable must not exceed 8,000 bytes, this series is only logically handled in the SQL Server Syntax Analyzer without occupying physical memory. For example, the statement never generates a string of 16,000 concatenations:
EXEC ('name_of_8000_char_string' 'another_name_of_8000_char_string')
The statement in the execute statement is not compiled before the execute statement is executed. Changes in the database environment are only valid before the EXECUTE statement. For example, after this example of Exec, the database environment is MASTER:
Use master exec ("use pubs" Select * from authors permissions
The execute permission of the stored procedure defaults to the owner of the stored procedure, which can transfer this permissions to other users. When encountering an Execute statement, even if the execute statement is in the stored procedure, the permission to use this statement within the Execute string is also checked. When running a stored procedure for performing a string, the system is permitted in the user environment of the process instead of creating the user environment. However, if a user has two stored procedures, and the first process calls the second process, the Execute permission check is not performed during the second process.
Example
A. Pass a single parameter using Execute
Showind stored procedures requires parameters (@tabname), which is the name of a table. The following example executes the Showind stored procedure to Titles as the parameter value.
Note that the Showind stored procedure is only used as an example, and the PUBS database does not have this process.
EXEC Showind Titles can explicitly naming during execution: exec showind @tabname = titles
If this is the first statement in ISQL script or batch, the exec statement can be omitted:
Showind Titles- or -showind @tabname = Titles
B. Use multiple parameters with an output parameter to execute the Roy_CHECK stored procedure, pass three parameters. The third parameter @PC is the output parameter. After the process is executed, the return variable can be obtained from the variable @Percent.
Note that the ROY_CHECK stored procedure is only used as an example, and there is no such process in the PUBS database.
Declare @Percent Int
Execute Roy_Check 'bu1032', 1050, @PC = @Percent Output
Set percent = @Percent
C. Use an execute 'tsql_string' statement with a variable to display the Execute statement how to process the dynamically generated, a string containing the variable. This example creates a TABLES_CURSOR cursor to hold a list of all user-defined tables (Type = u).
This example is used only as an example.
DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tables_cursor DECLARE @tablename sysname FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@ FETCH_STATUS <> -1) BEGIN / * A @@ FETCH_STATUS of -2 means that the row has been deleted. There is no need to test for this because this loop drops all user-defined tables. * /. EXEC ( 'DROP TABLE' @tablename) FETCH NEXT FROM tables_cursor INTO @tablename END PRINT 'All user-defined Tables have been Dropped from the database. 'deallocate tables_cursord. This example is performed on the remote server SQLServer1 on the remote server SQLServer1, saves the return status in @retstat, indicating that the run is successful or failed. DECLARE @retstat int execute @retstat = sqlserver1.pubs.dbo.checkcontract '409-56-4008'E. Using the XP_cmdshell extension stored procedure using the XP_cmdshell extension stored procedure to list all files the catalog.
Use master execute xp_cmdshell 'dir * .exe'f. This example creates a variable that represents stored procedure names with an Execute statement with a stored procedure variable.
Declare @Proc_name varchar (30) set @proc_name = 'sp_who' exec @Proc_nameg. This example is created with a stored procedure, the first and third parameters in the process are default. When the process is run, if the value is not transmitted or the default value is specified, these default values are assigned to the first and third parameters. Note that the default keyword has multiple usage methods.
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc_calculate_taxes' AND type = 'P') DROP PROCEDURE proc_calculate_taxes GO - Create the stored procedure CREATE PROCEDURE proc_calculate_taxes (@ p1 smallint = 42, @ p2 char (1),. @ p3 varchar (8) = 'car') AS SELECT * from myTableProc_calculate_taxes stored procedures can be performed in a variety of combinations:
