EXECUTE (T-SQL)

Executes a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch.

Syntax

Execute a stored procedure:

[[EXEC[UTE]]
    {
        [@return_status =]
            {procedure_name [;number] | @procedure_name_var
    }
    [[@parameter =] {value | @variable [OUTPUT] | [DEFAULT]]
        [,...n]
[WITH RECOMPILE]

Execute a character string:

EXEC[UTE] ({@string_variable | [N]'tsql_string'} [+...n])

Arguments

@return_status

Is an optional integer variable that stores the return status of a stored procedure. This variable must be declared in the batch or stored procedure before it is used in an EXECUTE statement.

procedure_name

Is the fully qualified or nonfully qualified name of the stored procedure to call. Procedure names must conform to the rules for identifiers. For more information, see Using Identifiers. The names of extended stored procedures are always case-sensitive, regardless of the code page or sort order of the server.

A procedure that has been created in another database can be executed if the user executing the procedure owns the procedure or has the appropriate permission to execute it in that database. A procedure can be executed on another server running Microsoft® SQL Server™ if the user executing the procedure has the appropriate permission to use that server (remote access) and to execute the procedure in that database. If a server name is specified but no database name is specified, SQL Server looks for the procedure in the user’s default database.

;number

Is an optional integer used to group procedures of the same name so they can be dropped with a single DROP PROCEDURE statement. This parameter is not used for extended stored procedures.

Procedures used in the same application are often grouped this way. For example, the procedures used with the orders application may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. After the procedures have been grouped, individual procedures within the group cannot be dropped. For example, the statement DROP PROCEDURE orderproc;2 is not allowed. For more information about procedure groups, see CREATE PROCEDURE.

@procedure_name_var

Is the name of a locally defined variable that represents a stored procedure name.

@parameter

Is the parameter for a procedure, as defined in the CREATE PROCEDURE statement. Parameter names must be preceded by the at sign (@). When used with the @parameter_name = value form, parameter names and constants do not have to be supplied in the order in which they are defined in the CREATE PROCEDURE statement. However, if the @parameter_name = value form is used for any parameter, it must be used for all subsequent parameters.

Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs (for example, inserting into a column that does not allow NULLs), SQL Server generates an error. To prevent passing a parameter value of NULL to a column that does not allow NULLs, either add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.

value

Is the value of the parameter to the procedure. If parameter names are not specified, parameter values must be supplied in the order defined in the CREATE PROCEDURE statement.

If the value of a parameter is an object name, character string, or qualified by a database name or owner name, the entire name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.

If a default is defined in the CREATE PROCEDURE statement, a user can execute the procedure without specifying a parameter. The default must be a constant and can include the wildcard characters %, _, [ ], and [^] if the procedure uses the parameter name with the LIKE keyword.

The default can also be NULL. Usually, the procedure definition specifies the action that should be taken if a parameter value is NULL.

@variable

Is the variable that stores a parameter or a return parameter.

OUTPUT

Specifies that the stored procedure returns a parameter. The matching parameter in the stored procedure must also have been created with the keyword OUTPUT. Use this keyword when using cursor variables as parameters.

If OUTPUT parameters are being used and the intent is to use the return values in other statements within the calling batch or procedure, the value of the parameter must be passed as a variable (that is, @parameter = @variable). You cannot execute a procedure specifying OUTPUT for a parameter that is not defined as an OUTPUT parameter in the CREATE PROCEDURE statement. Constants cannot be passed to stored procedures using OUTPUT; the return parameter requires a variable name. The variable’s data type must be declared and a value assigned before executing the procedure. Return parameters can be of any data type except the text or image data types.

DEFAULT

Supplies the default value of the parameter as defined in the procedure. When the procedure expects a value for a parameter that does not have a defined default and either a parameter is missing or the DEFAULT keyword is specified, an error occurs.

n

Is a placeholder indicating that the preceding item(s) can be repeated multiple times. For example, EXECUTE can specify one or more @parameter, value, or @variable items.

WITH RECOMPILE

Forces a new plan to be compiled. Use this option if the parameter you are supplying is atypical or if the data has significantly changed. The changed plan is used in subsequent executions. This option is not used for extended stored procedures. It is recommended that you use this option sparingly because it is expensive.

@string_variable

Is the name of a local variable. @string_variable can be of char, varchar, nchar, or nvarchar data type with a maximum value of the server’s available memory. If the string is greater than 4,000 characters, concatenate multiple local variables to use for the EXECUTE string. For more information about system-supplied SQL Server data types, see Data Types.

[N]'tsql_string'

Is a constant string. tsql_string can be of nvarchar or varchar data type. If the N is included, the string is interpreted as nvarchar data type with a maximum value of the server’s available memory. If the string is greater than 4,000 characters, concatenate multiple local variables to use for the EXECUTE string.

Remarks

If the first three characters of the procedure name are sp_, SQL Server searches the master database for the procedure. If no qualified procedure name is provided, SQL Server searches for the procedure as if the owner name is dbo. To resolve the stored procedure name as a user-defined stored procedure with the same name as a system stored procedure, provide the fully qualified procedure name.

Parameters can be supplied either by using value or by using @parameter_name = value. A parameter is not part of a transaction; therefore, if a parameter is changed in a transaction that is later rolled back, the parameter’s value does not revert to its previous value. The value returned to the caller is always the value at the time the procedure returns.

Nesting occurs when one stored procedure calls another. The nesting level is incremented when the called procedure begins execution, and it is decremented when the called procedure has finished. Exceeding the maximum of 32 nesting levels causes the entire calling procedure chain to fail. The current nesting level is stored in the @@NESTLEVEL function.

SQL Server currently uses return values 0 through -14 to indicate the execution status of stored procedures. Values from -15 through -99 are reserved for future use. For more information about a list of reserved return status values, see RETURN.

Because remote stored procedures and extended stored procedures are not within the scope of a transaction (unless issued within a BEGIN DISTRIBUTED TRANSACTION statement or when used with various configuration options), commands executed through calls to them cannot be rolled back. For more information, see System Stored Procedures and BEGIN DISTRIBUTED TRANSACTION.

When using cursor variables, if you execute a procedure that passes in a cursor variable with a cursor allocated to it an error occurs.

You do not have to specify the EXECUTE keyword when executing stored procedures if the statement is the first one in a batch.

Using EXECUTE with a Character String

Use the string concatenation operator (+) to create large strings 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 be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:

EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE statement is executed.

Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this example, the database context is master:

USE master EXEC ("USE pubs") SELECT * FROM authors

Permissions

EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.

Examples

A. Use EXECUTE to pass a single parameter

The showind stored procedure expects one parameter (@tabname), a table name. The following examples execute the showind stored procedure with titles as its parameter value.

Note The showind stored procedure is shown for illustrative purposes only and does not exist in the pubs database.

EXEC showind titles

The variable can be explicitly named in the execution:

EXEC showind @tabname = titles

If this is the first statement in a batch or an isql script, EXEC is not required:

showind titles

Or

showind @tabname = titles

B. Use multiple parameters and an output parameter

This example executes the roy_check stored procedure, which passes three parameters. The third parameter, @pc, is an OUTPUT parameter. After the procedure has been executed, the return value is available in the variable @percent.

Note The roy_check stored procedure is shown for illustrative purposes only and does not exist in the pubs database.

DECLARE @percent int

EXECUTE roy_check 'BU1032', 1050, @pc = @percent OUTPUT

SET Percent = @percent

C. Use EXECUTE ‘tsql_string’ with a variable

This example shows how EXECUTE handles dynamically built strings containing variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).

Note This example is shown 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 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_cursor

D. Use EXECUTE with a remote stored procedure

This example executes the checkcontract stored procedure on the remote server SQLSERVER1 and stores the return status indicating success or failure in @retstat.

DECLARE @retstat int

EXECUTE @retstat = SQLSERVER1.pubs.dbo.checkcontract '409-56-4008'

E. Use EXECUTE with an extended stored procedure

This example uses the xp_cmdshell extended stored procedure to list a directory of all files with an .exe file extension.

USE master

EXECUTE xp_cmdshell 'dir *.exe'

F. Use EXECUTE with a stored procedure variable

This example creates a variable that represents a stored procedure name.

DECLARE @proc_name varchar(30)

SET @proc_name = 'sp_who'

EXEC @proc_name

G. Use EXECUTE with DEFAULT

This example creates a stored procedure with default values for the first and third parameters. When the procedure is run, these defaults are inserted for the first and third parameters if no value is passed in the call or if the default is specified. Note the various ways the DEFAULT keyword can be used.

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 mytable

The proc_calculate_taxes stored procedure can be executed in many combinations:

EXECUTE proc_calculate_taxes @p2 = 'A'
EXECUTE proc_calculate_taxes 69, 'B'
EXECUTE proc_calculate_taxes 69, 'C', 'House'
EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = 'D'
EXECUTE proc_calculate_taxes DEFAULT, @p3 = 'Local', @p2 = 'E'
EXECUTE proc_calculate_taxes 69, 'F', @p3 = DEFAULT
EXECUTE proc_calculate_taxes 95, 'G', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'H', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'I', @p3 = DEFAULT

See Also