Aim :- In this article we are going to learn one of the important statements used in SQL Server. The statement we will discuss here is NOEXEC. Firstly, we will understand the use of SET NOEXECstatement in SQL Server. Later, we will see how we can implement this statement.
Description :- NOEXEC – No Execution | When we set NOEXEC statement in SQL Server to “ON”, it compiles each SQL query but will not execute it. This means that it will check syntactical errors at the time of executing a query, but it will not execute the query for result.
When should we use SET NOEXEC statement in SQL Server?
Suppose, we have a big SQL script with tons of code and you want to make sure it runs but you don’t want to execute it because it may update tables, delete data etc.
In Detail– SET NOEXEC statement in SQL Server-
The execution of statements in SQL Server has two phases:-
- Compilation
- Execution
This setting is useful for SQL Server to validate the syntax and object names in Transact-SQL (TSQL) 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 statement in SQL Server is set at execute or run time and not at parse time.
Learn – SET NOEXEC statement in SQL Server with help of Examples
Case1:- Without SET EXEC statement
Select getdate()
Go
Select ab2/123
Go
Go
Select ab2/123
Go
Without Set NoExec statement in SQL Server
Note– In above screenshot you can see two error messages. One is saying that the 1 row(s) got affected and other is showing error message
In detail – About 2 error messages –
When you execute both the SQL statements together, you will notice that the 1st statement i.e. (select getdate()) will execute properly and gives result as today’s date.
But second statement didn’t give any result, instead it throws an error message. As our SQL code have limited lines code which makes easy for us to identify that the second statement is invalid. But it gives hard times to identify the errors if we have thousand lines of code or more than that.
Case2(a) :- With SET EXEC statement on less SQL queries
Set NoExec ON
Go
Select getdate()
Go
Select ab2/123
Go
Go
Select getdate()
Go
Select ab2/123
Go
With Set NoExec statement in SQL Server
Note– In above screen shot we can clearly observe that we had only one Error message even though the code executed is the same as in Case 1.
This is how SET NOEXEC statement in SQL Server will work. It is going to compile each SQL statement and list out the error messages. With SET NOEXEC statement in SQL Server, we can find SQL statements having syntactical errors without being executed for result.
Let’s take another example for more SQL queries by using SET NOEXEC statement ON
Case2(b) :- With SET EXEC statement on more SQL queries
Set NoExec ON
Go
Select getdate()
Go
Select ab2/123
Go
Select GETDATE()-1
Go
select GETDATE()
Go
Select getdate()
Go
Select ab2/123
Go
Select GETDATE()-1
Go
select GETDATE()
More SQL queries With Set NoExec statement in SQL Server
In the above screenshot we have 4 SQL statements and SET NO EXEC statement is ON. Out of 4 SQL Statements, first and thrid statements are valid whereas second and fourth statements are not valid. Here, we can observer the two error messages – For Second and Forth statements, but unable to see the result set of first and third statements.
Hope you all got the difference between using SET NOEXEC statement in SQL Server and without using SET NOEXEC statement in SQL Server. With this, we come to an end for the post on Set NoExec statement in SQL Server. Please give it a try at your end and drop your valuable feedbacks or suggestions as comments below.
