SET NOEXEC (T-SQL)

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

-- 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 syntax'

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

-- Reset SET NOEXEC to OFF.

SET NOEXEC OFF

GO

See Also