How Set NOEXEC on statement works

Set NOEXEC ON:- This statement is used to compile the SQL queries without executing them, using this statement we can compile a SQL query and check if there is a syntax error or not.

Points to Remember: -

• If NOEXEC is ON SQL server complies all the T-SQL batches but not execute them and this option is OFF SQL server compiles the batches first then execute them.
• Setting up NOEXEC ON only applies to current session not all the sessions.
• By default NOEXEC is OFF.

Let’s take an example to understand this fact.

I have created employee table and inserted few rows into it using below statements.

create table Employee(Eid int, Name varchar(50))

insert into Employee values(101,'Neeraj')

insert into Employee values(102,'Neha')

insert into Employee values(103,'Rachit')

We will test two scenarios first with correct syntax and second with incorrect syntax to test the effect of setting up NOEXEC ON.

Set NOEXEC ON

Select * from Employee

Set NOEXEC Off

Employee table has three rows into it, but in the above snapshot there is no result set showing as we have set the NOEXEC option ON which compiled the query but not executed it.

Let’s take an example of a query that is syntactically incorrect and see the result.

Set NOEXEC ON

Select  from Employeeà ( I have removed [*] Symbol from the query)

Set NOEXEC Off

We can see in the above screen shot that error message is coming “incorrect syntax near the keyword from” as [*] symbol is missing from the query.

I hope, I was able to make you understand about set NOEXEC statement. Please feel free to add your comments to share your thoughts on this article.