In SQL Server, the EXEC command is used to execute stored procedures or dynamic SQL statements. Here’s a brief overview of how to use it in both scenarios:
Executing a Stored Procedure
To execute a stored procedure, you simply specify the procedure name followed by any necessary parameters.
Example:
Suppose you have a stored procedure named GetEmployeeDetails that takes an employee ID as a parameter:
EXEC GetEmployeeDetails @EmployeeID = 123;
You can also omit the EXEC keyword in some cases:
GetEmployeeDetails @EmployeeID = 123;
Executing Dynamic SQL
To execute a dynamic SQL statement, you'll often use the EXEC command or the sp_executesql stored procedure.
Using EXEC
You can execute a dynamic SQL string directly with the EXEC command:
Example:
DECLARE @sql NVARCHAR(1000);
SET @sql = 'SELECT * FROM Employees WHERE EmployeeID = 123';
EXEC @sql;
Using sp_executesql
Using sp_executesql is preferred for dynamic SQL because it allows for parameterization, which can help prevent SQL injection and can improve performance through better execution plan reuse.
Example:
DECLARE @sql NVARCHAR(1000);
DECLARE @EmployeeID INT = 123;
SET @sql = 'SELECT * FROM Employees WHERE EmployeeID = @EmployeeID';
EXEC sp_executesql @sql, N'@EmployeeID INT', @EmployeeID;
Summary
- Use EXEC to call stored procedures directly.
- Use EXEC for dynamic SQL or consider using sp_executesql for parameterized dynamic SQL to improve security and performance.
Keep in mind the differences in execution context and the scope of variables when you use dynamic SQL, especially when creating more complex queries or working with multiple statements.