SET NOEXEC (Transact-SQL)
Compiles each query but does not execute it.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
SET NOEXEC { ON | OFF }
Remarks
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.
Permissions
Requires membership in the public role.
Examples
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
See Also
Reference
SET (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)
SET SHOWPLAN_TEXT (Transact-SQL)