SQL Server Documentation

Transact-SQL Reference

EXECUTE Statement

Executes a Transact-SQL statement or a stored procedure.

Syntax

EXECUTE [] []
    {  |  |  }
    [ &  ]
    [ { , | ,& }  ] [ ...n ]
    [ WITH RECOMPILE | WITH EXECUTE ONLY ]

Where:

  • parameter is an optional parameter name.
  • variable is an optional variable name.
  • sql_batch is a Transact-SQL statement or batch.
  • stored_procedure is a stored procedure name.
  • extended_stored_procedure is an extended stored procedure name.
  • variable_assignments is an assignment of a variable.
  • ...n indicates that multiple parameters can be specified.

Description

The EXECUTE (or EXEC) statement runs a Transact-SQL statement, stored procedure, or extended stored procedure.

You can pass parameters to the statement or procedure being executed. If the statement or procedure returns a value, it can be captured into a variable.

Examples

Example 1: Executing a Stored Procedure

EXECUTE sp_who;

Example 2: Executing a Stored Procedure with Parameters

DECLARE @DatabaseName SYSNAME = 'AdventureWorks';
EXECUTE sp_spaceused @DatabaseName;

Example 3: Executing a Dynamic SQL Batch and Capturing Output

DECLARE @SQLStatement NVARCHAR(500);
DECLARE @Result INT;

SET @SQLStatement = N'SELECT COUNT(*) FROM Production.Product';

EXECUTE sp_executesql @SQLStatement, N'@OutputCount INT OUTPUT', @OutputCount = @Result OUTPUT;

SELECT @Result AS ProductCount;

Example 4: Executing a T-SQL Statement

EXECUTE ('USE master; SELECT DB_NAME() AS CurrentDatabase;');

Remarks

  • The EXECUTE statement is often used to run dynamic SQL.
  • When executing dynamic SQL, it's recommended to use sp_executesql for better performance and security (prevents SQL injection).
  • Extended stored procedures are system procedures that extend the functionality of SQL Server.
  • The WITH RECOMPILE option recompiles the execution plan for the statement or procedure.
  • The WITH EXECUTE ONLY option restricts execution to the user executing it and does not allow re-execution without recompilation.

See Also