SET NOEXEC (Transact-SQL)
Compiles each query but does not execute it.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
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.
The setting of SET NOEXEC is set at execute or run time and not at parse time.
The following example uses NOEXEC with a valid query, a query with an object name that is not valid, and a query with incorrect syntax.
USE AdventureWorks2012; GO PRINT 'Valid query'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Inner join. 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 to OFF. SET NOEXEC OFF; GO PRINT 'Invalid object name'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Function name uses is a reserved keyword. USE AdventureWorks2012; GO CREATE FUNCTION dbo.Values(@BusinessEntityID int) RETURNS TABLE AS RETURN (SELECT PurchaseOrderID, TotalDue FROM dbo.PurchaseOrderHeader WHERE VendorID = @BusinessEntityID); -- SET NOEXEC to OFF. SET NOEXEC OFF; GO PRINT 'Invalid syntax'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Built-in function incorrectly invoked. SELECT * FROM fn_helpcollations; -- Reset SET NOEXEC to OFF. SET NOEXEC OFF; GO