Overview

• When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.
• When SET NOEXEC is OFF, all batches are executed after compilation.
• The execution of statements in SQL Server has two phases: compilation and execution.
• This setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing.
• It is also useful for debugging statements that would generally be part of a larger batch of statements.

SET NOEXEC ON;
GO

SELECT e.BusinessEntityID, e.JobTitle, v.Name
FROM HumanResources.Employee AS e 
   INNER JOIN Purchasing.PurchaseOrderHeader AS poh
   ON e.BusinessEntityID = poh.EmployeeID
   INNER JOIN Purchasing.Vendor AS v
   ON poh.VendorID = v.BusinessEntityID;
GO

SET NOEXEC OFF;
GO

CREATE FUNCTION dbo.Values(@BusinessEntityID int)
RETURNS TABLE
AS
RETURN (SELECT PurchaseOrderID, TotalDue
   FROM dbo.PurchaseOrderHeader
   WHERE VendorID = @BusinessEntityID);
GO

SET NOEXEC ON;
GO

-- Built-in function incorrectly invoked.
SELECT * FROM fn_helpcollations;

SET NOEXEC OFF;