SET NOEXEC
Compiles each query but does not execute it.
Syntax
SET NOEXEC { ON | OFF }
Remarks
When SET NOEXEC is ON, Microsoft® 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 consists of 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 usually 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
SET NOEXEC permissions default to all users.
Examples
This example uses NOEXEC with a valid query, a query with an invalid object name, and a query with incorrect syntax.
USE pubs
GO
PRINT 'Valid query'
GO
-- SET NOEXEC to ON.
SET NOEXEC ON
GO
-- Inner join.
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
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 pubs
GO
CREATE FUNCTION values (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
-- 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