SQL Server Books Online: executing stored procedures

Server| Stored Procedure | Execute Execute
A user-defined function, system procedure, user-defined stored procedure, or extended stored procedure that performs a scalar value. Also supports the execution of strings within Transact-SQL batches

To invoke a function, use the syntax described in EXECUTE stored_procedure.
Grammar
Execute the stored procedure: [[EXEC [UTE]]
{
[@return_status =]
{procedure_name [; number] | @procedure_name_var
}
[[@parameter =] {value | @variable [OUTPUT] | [DEFAULT]]
[,... N]
[With RECOMPILE] execution string: EXEC [UTE] ({@string_variable | [n] ' tsql_string '} [+.. N]] Parameter
@return_status
is an optional integer variable that holds the return status of the stored procedure. This variable must be declared in a batch, stored procedure, or function before it is used for an EXECUTE statement.

When used to invoke a scalar-valued user-defined function, the @return_status variable can be any scalar data type.

Procedure_name
is the fully valid or incomplete legal name of the stored procedure to be invoked. The procedure name must conform to the rules for identifiers. For more information, see Using Identifiers. The name of an extended stored procedure is always case-sensitive, regardless of the server's code page or Sort method.
A user can perform a procedure created in another database, as long as the user owns the procedure or has the appropriate permissions to execute it in the database. A user can execute a procedure on another server running Microsoft®sql Server™, as long as the user has the appropriate permissions to use the server (remote access) and can execute the procedure in the database. If you specify a server name but do not specify a database name, SQL Server looks for the procedure in the user's default database.

; number
is an optional integer used to combine procedures of the same name so that they can be dropped with a drop PROCEDURE statement. This parameter cannot be used for extended stored procedures.
Processes that are used in the same application are generally grouped in that way. For example, a procedure used in an order application can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement drops the entire group. After you group A procedure, you cannot drop a single procedure in the group. For example, DROP PROCEDURE orderproc;2 is not allowed. For more information about process groups, see CREATE PROCEDURE.

@procedure_name_var
is a locally defined variable name that represents the stored procedure name.

@parameter
is a process parameter, defined in the CREATE PROCEDURE statement. The parameter name must precede the symbol (@). When used in @parameter_name = value format, parameter names and constants do not necessarily appear in the order defined in the CREATE PROCEDURE statement. However, if you have a parameter that uses the @parameter_name = value format, all other parameters must use this format.
By default, the parameter can be empty. If you pass a null parameter value, and the parameter is used for a column in the CREATE or ALTER TABLE statement that is not allowed to be null (for example, inserting to a column that is not allowed to be null), SQL Server complains. To avoid passing NULL parameter values to columns that are not allowed to be null, you can add program design logic to the procedure or take the default value (using the the default keyword in the CREATE or ALTER TABLE statement).

Value
Is the value of the parameter in the procedure. 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 is restricted by a database name or 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 you define a default value in the CREATE PROCEDURE statement, the user does not have to specify parameters when executing the procedure. If the procedure uses a parameter name with the LIKE keyword, the default value must be a constant and can contain%, _, [], and [^] wildcard characters.
The default value can also be NULL. Typically, a procedure definition specifies an action that should be performed when the parameter value is NULL.

@variable
Is the variable that holds the parameter or returns the parameter.

OUTPUT
Specifies that the stored procedure must return a parameter. The matching parameters of the stored procedure must also be created by the keyword OUTPUT. Use this keyword when using a cursor variable as a parameter.
If you use an OUTPUT parameter to use its return value in another statement that invokes a batch or procedure, the parameter value must be passed as a variable (that is, @parameter = @variable). If a parameter is not defined as an output parameter in the CREATE PROCEDURE statement, the procedure for specifying output for that parameter cannot be performed. A constant cannot be passed to a stored procedure using OUTPUT, and a variable name is required for the return parameter. Before executing the procedure, you must declare the data type of the variable and assign a value. The return parameter can be any data type other than the text or image data type.

DEFAULT
Provides a default value for the parameter, based on the definition of the procedure. An error occurs when a procedure requires a parameter value that does not have a predefined default value, or if a parameter is missing, or if the default keyword is specified.

N
is a placeholder that indicates that the item in front of it can be repeatedly executed multiple times. For example, the EXECUTE statement can specify one or more @parameter, value, or @variable.

With RECOMPILE
Forces a new plan to be compiled. Use this option if the supplied parameter is not a typical parameter or the data has changed significantly. Use the changed schedule in future program execution. This option is not available for extended stored procedures. It is recommended that you use this option as sparingly as possible because it consumes more system resources.

@string_variable
is the name of the local variable. The @string_variable can be a char, varchar, nchar, or nvarchar data type, and the maximum is the server's available memory. If the string is longer than 4,000 characters, multiple local variables are concatenated together for the EXECUTE string. For more information about the SQL Server data types provided by the system, see Data types.

[N] ' tsql_string '
is a constant, tsql_string can be a nvarchar or varchar data type. If it contains N, the string is interpreted as a nvarchar data type, and the maximum is the server's available memory. If the string is longer than 4,000 characters, multiple local variables are concatenated together for the EXECUTE string.
Comments
If the first three characters of the procedure name are Sp_,sql Server, the process is found in the Master database. If a valid procedure name is not found, SQL Server looks for a procedure with the owner name dbo. To resolve the stored procedure name to a user-defined stored procedure with the same name as the system stored procedure, provide a fully legal procedure name.

Parameters can be supplied by using value or @parameter_name = value. parameter is not part of a transaction, so if the value of the parameter in the transaction changes and the transaction rolls back later, the parameter value does not fall back to the previous value. The value returned to the caller is always the value when the procedure returns.

Nesting occurs when a stored procedure invokes another stored procedure. When the invoked procedure begins to execute, the nesting level increases, and the nesting level decreases when the calling procedure finishes executing. A nesting level of up to 32 levels, exceeding 32 levels, causes the entire call chain to fail. The current nesting level is stored in the @ @NESTLEVEL function.

SQL Server currently uses a return value of 0 through-14来 to represent the execution state of a stored procedure. Value –15 to-99 for later use. For more information about the list of reserved returned status values, see return.

Because remote stored procedures and extended stored procedures are not in the scope of the transaction (unless they are emitted in the BEGIN distributed TRANSACTION statement or are used with different configuration options), the command executed by the call cannot be rolled back. For more information, see System stored Procedures and BEGIN distributed TRANSACTION.

When a cursor variable is used, an error occurs if the executing procedure passes a cursor variable with a cursor assigned to it.

When you execute a stored procedure, if the statement is the first statement in the batch, you do not have to specify the EXECUTE keyword.
Using the EXECUTE command with string
Use the string concatenation operator (+) to create a long string for dynamic execution. 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 concatenation is only logically processed in SQL Server parser and does not occupy physical memory. For example, the statement never generates a string that is 16,000 concatenated: EXEC (' name_of_8000_char_string ' + ' another_name_of_8000_char_string ')

Statements within an EXECUTE statement are not compiled until the EXECUTE statement is executed.
Changes to the database environment are valid only until the EXECUTE statement ends. For example, after EXEC in this example, the database environment is Master:use Master EXEC ("use pubs") SELECT * from authors
Permissions
The EXECUTE permission for the stored procedure defaults to the owner of the stored procedure, who can transfer this permission to another user. When an EXECUTE statement is encountered, the permission to use the statement within the Execute string is checked even if the EXECUTE statement is in the stored procedure. When you run a stored procedure that executes a string, the system checks the permissions in the user environment that executes the procedure, not in the user environment in which the procedure is created. However, if a user has two stored procedures, and the first procedure calls the second procedure, the EXECUTE permission check is not performed in the second procedure.
Example A. Passing a single parameter using EXECUTE
The showind stored procedure requires a parameter (@tabname), which is the name of a table. The following example executes the showind stored procedure, with the titles as the parameter values.





Description The showind stored procedure is only used as an example, the pubs database does not have this process.



EXEC Showind Titles
Variables can be named explicitly during execution:
EXEC Showind @tabname = titles


If this is the first statement in a isql script or batch, the EXEC statement can omit:

Showind titles
Or
Showind @tabname = titles

B. Using multiple parameters with one output parameter
This example executes the Roy_check stored procedure, passing three parameters. The third parameter @pc is the output parameter. After the procedure has been executed, the return variable can be @percent from the variable
Get.



Explains that the roy_check stored procedure is used only as an example and does not have this procedure in the pubs database.



DECLARE @percent intexecute roy_check ' BU1032 ', 1050, @pc = @percent outputset percent = @percent
C. Using the EXECUTE ' tsql_string ' statement with a variable
This example shows how the EXECUTE statement handles dynamically generated strings that contain variables. This example creates a tables_cursor cursor to hold all user-defined tables
A list of (type = U).



This example is used for illustrative purposes only. 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 row has been deleted.
There is no need to test in this because the 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_cursor
D. Using an EXECUTE statement with a remote stored procedure
This example executes the checkcontract stored procedure on the remote server SQLSERVER1, saving the return status in the @retstat, indicating that the operation succeeded or failed.

DECLARE @retstat int
EXECUTE @retstat = SQLSERVER1.pubs.dbo.checkcontract ' 409-56-4008 '
E. Using an EXECUTE statement with extended stored procedures
The following example uses xp_cmdshell extended stored procedures to list the directories of all files with the file name extension. exe.

Use master
EXECUTE xp_cmdshell ' dir *.exe '
F. Use an EXECUTE statement with a stored procedure variable
This example creates a variable that represents the name of the stored procedure.

DECLARE @proc_name varchar (30)
SET @proc_name = ' sp_who '
EXEC @proc_name

G. Using the EXECUTE statement with DEFAULT
This example creates a stored procedure in which the first and third parameters are the default values. When this procedure is run, if the call does not pass a value or specifies a default value,
These default values are assigned to the first and third parameters. Note that the DEFAULT keyword has several ways to use it.

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 = @p2 char (1),
@p3 varchar (8) = ' car ')
As
SELECT *
From MyTable

Proc_calculate_taxes stored procedures can be performed in a variety of combinations: Execute proc_calculate_taxes @p2 = ' A '
EXECUTE proc_calculate_taxes, ' B '
EXECUTE proc_calculate_taxes, ' C ', ' house '
EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = ' D '
EXECUTE proc_calculate_taxes DEFAULT, @p3 = ' local ', @p2 = ' E '
EXECUTE proc_calculate_taxes, ' F ', @p3 = DEFAULT
EXECUTE proc_calculate_taxes, ' G ', DEFAULT
EXECUTE proc_calculate_taxes default, ' H ', default
EXECUTE proc_calculate_taxes default, ' I ', @p3 = default