SET NOEXEC (Transact-SQL)
Compiles each query but does not execute it.
When SET NOEXEC is ON, SQL Server 2005 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 AdventureWorks; GO PRINT 'Valid query'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Inner join. SELECT e.EmployeeID, e.Title, v.Name FROM HumanResources.Employee e INNER JOIN Purchasing.PurchaseOrderHeader poh ON e.EmployeeID = poh.EmployeeID INNER JOIN Purchasing.Vendor v ON poh.VendorID = v.VendorID; GO -- SET NOEXEC to OFF. SET NOEXEC OFF; GO PRINT 'Invalid object name'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Function name used is a reserved keyword. USE AdventureWorks; GO CREATE FUNCTION dbo.Values(@EmployeeID INT) RETURNS TABLE AS RETURN (SELECT PurchaseOrderID, TotalDue FROM dbo.PurchaseOrderHeader WHERE EmployeeID = @EmployeeID) -- 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