EXEC Command

• Executes a command string or character string within a Transact-SQL batch

DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);

sp_executesql

DECLARE @sql AS NVARCHAR(100) = 'SELECT * FROM Sales.Orders WHERE OrderId = @orderid;';
EXEC sp_executesql 
	@stmt = @sql,
	@params = N'@orderid AS INT',
	@orderid = 10248;

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.

IF OBJECT_ID(N'dbo.ProcTestDefaults', N'P')IS NOT NULL
    DROP PROCEDURE dbo.ProcTestDefaults;
GO

CREATE PROCEDURE ProcTestDefaults 
(
	@p1 smallint = 42, 
	@p2 char(1), 
	@p3 varchar(8) = 'CAR'
)
AS 
    SET NOCOUNT ON;
    SELECT @p1, @p2, @p3;
GO

EXECUTE ProcTestDefaults @p2 = 'A' WITH RECOMPILE;

EXEC WITH RESULT SET

• The WITH RESULT SET syntax changes the names and data types of the returning result set.

EXEC uspGetEmployeeManagers 16;

EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
( 
	(
		[Reporting Level] int NOT NULL,
		[ID of Employee] int NOT NULL,
		[Employee First Name] nvarchar(50) NOT NULL,
		[Employee Last Name] nvarchar(50) NOT NULL,
		[Employee ID of Manager] nvarchar(max) NOT NULL,
		[Manager First Name] nvarchar(50) NOT NULL,
		[Manager Last Name] nvarchar(50) NOT NULL 
	)
);