EXECUTE (Transact-SQL)
Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure.
Security Note |
|---|
Before you call EXECUTE with a character string, validate the character string. Never execute a command constructed from user input that has not been validated. For more information, see SQL Injection. |
SQL Server extends the EXECUTE statement so that it can be used to send pass-through commands to linked servers. Additionally, the context in which a string or command is executed can be explicitly set.
Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
{
[ @return_status = ]
{ module_name [ ;number ] | @module_name_var }
[ [ @parameter = ] { value
| @variable [ OUTPUT ]
| [ DEFAULT ]
}
]
[ ,...n ]
[ WITH RECOMPILE ]
}
[;]
Execute a character string
{ EXEC | EXECUTE }
( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
[ AS { LOGIN | USER } = ' name ' ]
[;]
Execute a pass-through command against a linked server
{ EXEC | EXECUTE }
( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ]
[ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]
)
[ AS { LOGIN | USER } = ' name ' ]
[ AT linked_server_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 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
You do not have to specify the EXECUTE keyword when you execute stored procedures when the statement is the first one in a batch.
SQL Server system stored procedures start with the characters sp_. They are physically stored in the Resource database, but logically appear in the sys schema of every system and user-defined database. When you execute a system stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with the sys schema name.
SQL Server system extended stored procedures start with the characters xp_, and these are contained in the dbo schema of the master database. When you execute a system extended stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with master.dbo.
When you execute a user-defined stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with a schema name. We do not recommend that you name a user-defined stored procedure with the same name as a system stored procedure. For more information about executing stored procedures, see Executing Stored Procedures (Database Engine).
Using EXECUTE with a Character String
In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.
Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this following statement is run, the database context is master.
USE master; EXEC ('USE AdventureWorks2008R2; SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee;');
Context Switching
You can use the AS { LOGIN | USER } = ' name ' clause to switch the execution context of a dynamic statement. When the context switch is specified as EXECUTE ('string') AS <context_specification>, the duration of the context switch is limited to the scope of the query being executed. For more information, see Understanding Context Switching.
Specifying a User or Login Name
The user or login name specified in AS { LOGIN | USER } = ' name ' must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the statement will fail. Additionally, IMPERSONATE permissions must be granted on the principal. Unless the caller is the database owner or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership. For example, assume the following conditions:
CompanyDomain\SQLUsers group has access to the Sales database.
CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database.
Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE @string_variable AS USER = 'CompanyDomain\SqlUser1' will fail because CompanyDomain\SqlUser1 does not exist as a principal in the database.
Best Practice
Specify a login or user that has the least privileges required to perform the operations that are defined in the statement or module. For example, do not specify a login name, which has server-level permissions, if only database-level permissions are required; or do not specify a database owner account unless those permissions are required.
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. For more information about impersonation within a module, see EXECUTE AS Clause (Transact-SQL).
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
To specify EXECUTE AS on a login, the caller must have IMPERSONATE permissions on the specified login name. To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. When no execution context is specified, or EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.
A. Using EXECUTE to pass a single parameter
The uspGetEmployeeManagers stored procedure expects one parameter (@BusinessEntityID). The following examples execute the uspGetEmployeeManagers stored procedure with BusinessEntityID 6 as its parameter value.
USE AdventureWorks2008R2; GO EXEC dbo.uspGetEmployeeManagers 6; GO
The variable can be explicitly named in the execution:
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 6; GO
If the following is the first statement in a batch or an osql or sqlcmd script, EXEC is not required.
dbo.uspGetEmployeeManagers 6; GO --Or dbo.uspGetEmployeeManagers @BusinessEntityID = 6; GO
B. Using multiple parameters
The following example executes the spGetWhereUsedProductID stored procedure. It passes two parameters: the first parameter is a product ID (819) and the second parameter, @CheckDate, is a datetime value.
USE AdventureWorks2008R2; GO DECLARE @CheckDate datetime; SET @CheckDate = GETDATE(); EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; GO
C. Using EXECUTE 'tsql_string' with a variable
The following example shows how EXECUTE handles dynamically built strings that contain variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables in the AdventureWorks2008R2 database, and then uses that list to rebuild all indexes on the tables.
USE AdventureWorks2008R2;
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;
EXECUTE ('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
The following example executes the uspGetEmployeeManagers stored procedure on the remote server SQLSERVER1 and stores the return status that indicates success or failure in @retstat.
DECLARE @retstat int; EXECUTE @retstat = SQLSERVER1.AdventureWorks2008R2.dbo.uspGetEmployeeManagers @BusinessEntityID = 6;
E. Using EXECUTE with a stored procedure variable
The following example creates a variable that represents a stored procedure name.
DECLARE @proc_name varchar(30); SET @proc_name = 'sys.sp_who'; EXEC @proc_name;
F. Using EXECUTE with DEFAULT
The following 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 when no value is passed in the call or when the default is specified. Note the various ways the DEFAULT keyword can be used.
USE AdventureWorks2008R2; 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 SET NOCOUNT ON; SELECT @p1, @p2, @p3 ; GO
The Proc_Test_Defaults stored procedure can be executed in many combinations.
-- 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
The following example passes a command string to a remote server. It creates a linked server SeattleSales that points to another instance of SQL Server and executes a DDL statement (CREATE TABLE) against that linked server.
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server' GO EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl (SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales; GO
H. Using EXECUTE WITH RECOMPILE
The following example executes the Proc_Test_Defaults stored procedure and forces a new query plan to be compiled, used, and discarded after the module is executed.
EXECUTE dbo.Proc_Test_Defaults @p2 = 'A' WITH RECOMPILE; GO
I. Using EXECUTE with a user-defined function
The following example executes the ufnGetSalesOrderStatusText scalar user-defined function. It uses the variable @returnstatus to store the value returned by the function. The function expects one input parameter, @Status. This is defined as a tinyint data type.
USE AdventureWorks2008R2; 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
The following example executes several SELECT statements at the remote Oracle server. The example begins by adding the Oracle server as a linked server and creating linked server login.
-- 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
The following example executes a Transact-SQL string that creates a table and specifies the AS USER clause to switch the execution context of the statement from the caller to User1. The Database Engine will check the permissions of User1 when the statement is run. User1 must exist as a user in the database and must have permission to create tables in the Sales schema, or the statement fails.
USE AdventureWorks2008R2;
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
The following example passes a command string to a remote server by using a question mark (?) placeholder for a parameter. The example creates a linked server SeattleSales that points to another instance of SQL Server and executes a SELECT statement against that linked server. The SELECT statement uses the question mark as a place holder for the ProductID parameter (952), which is provided after the statement.
-- Setup the linked server.
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
-- Execute the SELECT statement.
EXECUTE ('SELECT ProductID, Name
FROM AdventureWorks2008R2.Production.Product
WHERE ProductID = ? ', 952) AT SeattleSales;
GO
Security Note