SET NOEXEC ON statement in TSQL (SQL Server 2005)

When we execute any T-SQL statement(s) in SQL Server; it is accomplished by two processes:

  1. Compilation & then 2. Execution of the statement(s).

Compilation phase is useful for validating the syntax and object names in Transact-SQL for executing them properly. If it finds some error in it, execution of that batch will not take place. But if it passes the compilation; statements will be executed to give the results.

Sometimes we require not allowing some batch statements like a stored procedure to be executed; may be while creating / debugging it. Generally if we want to check the correctness of the statements we ‘Parse’ the batch; which provides the information about the correctness of its syntax or so. But this cannot restrict others to execute it.

So we can make use of the SET NOEXEC statement. When we SET it ON, following batch/queries will only be parsed, but will not be executed. If we want to execute them we will have to SET it OFF. By default it is OFF.

SET NOEXEC { ON | OFF }

Ex.

SET NOEXEC ON;

SQL Statements…..

GO;

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

One Response to SET NOEXEC ON statement in TSQL (SQL Server 2005)

  1. shan says:

    When SET NOEXEC is ON, SQL Server compiles–not just parse– each batch of Transact-SQL statements but does not execute them………

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s