@ return_status

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

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

module_name

Is the fully qualified or nonfully qualified name of the stored procedure or scalar-valued user-defined function to call. Module names must comply with the rules for identifiers. The names of extended stored procedures are always case-sensitive, regardless of the collation of the server.

A module that has been created in another database can be executed if the user running the module owns the module or has the appropriate permission to execute it in that database. A module can be executed on another server running SQL Server if the user running the module has the appropriate permission to use that server (remote access) and to execute the module in that database. If a server name is specified but no database name is specified, the SQL Server 2005 Database Engine looks for the module in the default database of the user.

; number

Is an optional integer that is used to group procedures of the same name. This parameter is not used for extended stored procedures.

Note:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

For more information about procedure groups, see CREATE PROCEDURE (Transact-SQL).

@ module_name_var

Is the name of a locally defined variable that represents a module name.

@ parameter

Is the parameter for module_name, as defined in the module. 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 module. However, if the @parameter_name=value form is used for any parameter, it must be used for all subsequent parameters.

By default, parameters are nullable. If a NULL parameter value is passed and that parameter is used in a CREATE TABLE or ALTER TABLE statement in which the column referenced does not allow for null values (for example, inserting into a column that does not allow null values), the Database Engine raises an error. To prevent passing a parameter value of NULL to a column that does not allow for null values, either add programming logic to the module or use a default value (by using the DEFAULT keyword of CREATE TABLE or ALTER TABLE) for the column.

value

Is the value of the parameter to pass to the module or pass-through command. If parameter names are not specified, parameter values must be supplied in the order defined in the module.

When executing pass-through commands against linked servers, the order of the parameter values depends on the OLE DB provider of the linked server. Most OLE DB providers bind values to parameters from left to right.

If the value of a parameter is an object name, character string, or qualified by a database name or schema name, the whole 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 module, a user can execute the module without specifying a parameter.

The default can also be NULL. Generally, the module 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 module or command string returns a parameter. The matching parameter in the module or command string must also have been created by using the keyword OUTPUT. Use this keyword when you use cursor variables as parameters.

If value is defined as OUTPUT of a module executed against a linked server, any changes to the corresponding @parameter performed by the OLE DB provider will be copied back to the variable at the end of the execution of module.

If OUTPUT parameters are being used and the intent is to use the return values in other statements within the calling batch or module, the value of the parameter must be passed as a variable, such as @parameter = @variable. You cannot execute a module by specifying OUTPUT for a parameter that is not defined as an OUTPUT parameter in the module. Constants cannot be passed to module by using OUTPUT; the return parameter requires a variable name. The data type of the variable must be declared and a value assigned before executing the procedure.

When EXECUTE is used against a remote stored procedure, or to execute a pass-through command against a linked server, OUTPUT parameters cannot be any one of the large object (LOB) data types.

Return parameters can be of any data type except the LOB data types.

DEFAULT

Supplies the default value of the parameter as defined in the module. When the module 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.

WITH RECOMPILE

Forces a new plan to be compiled, used, and discarded after the module is executed. If there is an existing query plan for the module, this plan remains in the cache.

Use this option if the parameter you are supplying is atypical or if the data has significantly changed. This option is not used for extended stored procedures. We recommend that you use this option sparingly because it is expensive.

Note:
You cannot use WITH RECOMPILE when calling a stored procedure that uses OPENDATASOURCE syntax. The WITH RECOMPILE option is ignored when a four-part object name is specified.

@ string_variable

Is the name of a local variable. @string_variable can be any char, varchar, nchar, or nvarchar data type. These include the (max) data types.

[N] 'tsql_string'

Is a constant string. tsql_string can be any nvarchar or varchar data type. If the N is included, the string is interpreted as nvarchar data type.

AS <context_specification>

Specifies the context in which the statement is executed. For more information, see Understanding Execution Context.

LOGIN

Specifies the context to be impersonated is a login. The scope of impersonation is the server.

USER

Specifies the context to be impersonated is a user in the current database. The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user.

Important:

While the context switch to the database user is active, any attempt to access resources outside the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database by using three- or four-part identifiers. To extend the scope of the context switch outside the current database, see Extending Database Impersonation by Using EXECUTE AS.

' name '

Is a valid user or login name. name must be a member of the sysadmin fixed server role or exist as a principal in sys.database_principals or sys.server_principals, respectively.

name cannot be a built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

For more information, see Specifying a User or Login Name later in this topic.

[N] 'command_string'

Is a constant string that contains the command to be passed through to the linked server. If the N is included, the string is interpreted as nvarchar data type.

[?]

Indicates parameters for which values are supplied in the <arg-list> of pass-through commands that are used in an EXEC('…', <arg-list>) AT <linkedsrv> statement.

AT linked_server_name

Specifies that command_string is executed against linked_server_name and results, if any, are returned to the client. linked_server_name must refer to an existing linked server definition in the local server. Linked servers are defined by using sp_addlinkedserver.

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 value of the parameter does not revert to its previous value. The value returned to the caller is always the value at the time the module returns.

Nesting occurs when one module calls another or executes managed code by referencing a common language runtime (CLR) module, user-defined type, or aggregate. The nesting level is incremented when the called module or managed code reference starts execution, and it is decremented when the called module or managed code reference has finished. Exceeding the maximum of 32 nesting levels causes the complete calling chain to fail. The current nesting level is stored in the @@NESTLEVEL system function.

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 (Transact-SQL) and BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

When you use 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 modules if the statement is the first one in a batch.

Using EXECUTE with Stored Procedures

Using EXECUTE with a Character String

USE master; EXEC ('USE AdventureWorks; SELECT EmployeeID, Title FROM HumanResources.Employee;');

Context Switching

Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables that are referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module.

EXECUTE permissions for a module default to the owner of the module, who can transfer them to other users. When a module is run that executes a string, permissions are checked in the context of the user who executes the module, not in the context of the user who created the module. However, if the same user owns the calling module and the module being called, EXECUTE permission checking is not performed for the second module. For more information, see Ownership Chains.

If the module accesses other database objects, execution succeeds when you have EXECUTE permission on the module and one of the following is true:

• The module is marked EXECUTE AS USER or SELF, and the module owner has the corresponding permissions on the referenced object.
  
• The module is marked EXECUTE AS CALLER, and you have the corresponding permissions on the object.
  
• The module is marked EXECUTE AS user_name, and user_name has the corresponding permissions on the object.
  

Context Switching Permissions

A. Using EXECUTE to pass a single parameter

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 6;
GO

EXEC dbo.uspGetEmployeeManagers @EmployeeID = 6;
GO

dbo.uspGetEmployeeManagers 6;
GO
--Or
dbo.uspGetEmployeeManagers @EmployeeID = 6;
GO

B. Using multiple parameters

USE AdventureWorks;
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

C. Using EXECUTE 'tsql_string' with a variable

USE AdventureWorks;
GO
DECLARE tables_cursor CURSOR
   FOR
   SELECT s.name, t.name 
   FROM sys.objects AS t
   JOIN sys.schemas AS s ON s.schema_id = t.schema_id
   WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @schemaname sysname;
DECLARE @tablename sysname;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
   EXEC ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;');
   FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
END;
PRINT 'The indexes on all tables have been rebuilt.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO

D. Using EXECUTE with a remote stored procedure

DECLARE @retstat int;
EXECUTE @retstat = SQLSERVER1.AdventureWorks.dbo.uspGetEmployeeManagers @EmployeeID = 6;

E. Using EXECUTE with a stored procedure variable

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

F. Using EXECUTE with DEFAULT

USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ProcTestDefaults', N'P')IS NOT NULL
   DROP PROCEDURE dbo.ProcTestDefaults;
GO
-- Create the stored procedure.
CREATE PROCEDURE dbo.ProcTestDefaults (
    @p1 smallint = 42, 
    @p2 char(1), 
    @p3 varchar(8) = 'CAR')
    AS 
   SELECT @p1, @p2, @p3
;
GO

-- Specifying a value only for one parameter (@p2).
EXECUTE dbo.ProcTestDefaults @p2 = 'A';
-- Specifying a value for the first two parameters.
EXECUTE dbo.ProcTestDefaults 68, 'B';
-- Specifying a value for all three parameters.
EXECUTE dbo.ProcTestDefaults 68, 'C', 'House';
-- Using the DEFAULT keyword for the first parameter.
EXECUTE dbo.ProcTestDefaults @p1 = DEFAULT, @p2 = 'D';
-- Specifying the parameters in an order different from the order defined in the procedure.
EXECUTE dbo.ProcTestDefaults DEFAULT, @p3 = 'Local', @p2 = 'E';
-- Using the DEFAULT keyword for the first and third parameters.
EXECUTE dbo.ProcTestDefaults DEFAULT, 'H', DEFAULT;
EXECUTE dbo.ProcTestDefaults DEFAULT, 'I', @p3 = DEFAULT;

G. Using EXECUTE with AT linked_server_name

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
EXECUTE ( 'CREATE TABLE AdventureWorks.dbo.SalesTbl 
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;
GO

H. Using EXECUTE WITH RECOMPILE

EXECUTE dbo.Proc_Test_Defaults @p2 = 'A' WITH RECOMPILE;
GO

I. Using EXECUTE with a user-defined function

USE AdventureWorks;
GO
DECLARE @returnstatus nvarchar(15);
SET @returnstatus = NULL;
EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2;
PRINT @returnstatus;
GO

J. Using EXECUTE to query an Oracle database on a linked server

-- Setup the linked server.
EXEC sp_addlinkedserver  
        @server='ORACLE',
        @srvproduct='Oracle',
        @provider='OraOLEDB.Oracle', 
        @datasrc='ORACLE10';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname='ORACLE',
    @useself='false', 
    @locallogin=null, 
    @rmtuser='scott', 
    @rmtpassword='tiger';
 
EXEC sp_serveroption 'ORACLE', 'rpc out', true;
GO
 
-- Execute several statements on the linked Oracle server.
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;
GO
DECLARE @v INT; 
SET @v = 7902;
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;
GO

K. Using EXECUTE AS USER to switch context to another user

USE AdventureWorks;
GO
EXECUTE ('CREATE TABLE Sales.SalesTable (SalesID int, SalesName varchar(10));')
AS USER = 'User1';
GO

L. Using a parameter with EXECUTE and AT linked_server_name

-- Setup the linked server.
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
-- Execute the SELECT statement.
EXECUTE ('SELECT ProductID, Name 
    FROM AdventureWorks.Production.Product
    WHERE ProductID = ? ', 952) AT SeattleSales;
GO

Reference

Other Resources

Help and Information