SET NOEXEC is one of my favorite SET statements. I am sure, when I explain it in detail you will love it too. Mostly, we come across in situations when we need to execute batch SQL statements (insert, update, delete) on production databases for the deployment / troubleshooting etc. Sometimes due to the syntax error, some parts of the batch statements execute and the remaining DO NOT and it creates the mess/headache for you to correct the data. And you are left with no option, but to restore the latest backup and fix the script and execute it again.
The solution for this problem is SET NOEXEC. It actually compiles the SQL Query and validate the syntax. The best/fun part is that it DOES NOT EXECUTE the SQL Query. So even if SQL Query is having errors, it DOES NOT affect the data and you are SAFE.
Let me create a sample to demonstrate, how it works.
USE AdventureWorks2014 GO --DROP TABLE tbl_sample --GO --Create a sample table CREATE TABLE tbl_sample ( [ID] INT, [Letter] VARCHAR(1) ) GO --Insert a record in the table INSERT INTO tbl_sample VALUES (1,'A') GO --Browse the data SELECT * FROM tbl_sample GO
Now, let me turn on the NOEXEC and execute few statements and view the result-set.
USE AdventureWorks2014 GO -- SET NOEXEC to ON SET NOEXEC ON; --Update the table UPDATE tbl_sample SET [Letter]='B' WHERE [ID]=1 GO --Delete the table DELETE FROM tbl_sample WHERE [ID]=1 GO -- SET NOEXEC to OFF SET NOEXEC OFF; GO
The output shows Command(s) completed successfully. It seems, that it not ONLY compiles but executes as well. Lets browse and check the impact of above queries on the data.
USE AdventureWorks2014 GO --Browse the data to check the impact. SELECT * FROM tbl_sample GO
As you can see above, the SQL Statements (Update & Delete) DID NOT IMPACT sample table data because NOEXEC was TURNED ON. Hence, it is PROVED that it ONLY compiles the SQL Statements and gives you either an error or Command(s) completed successfully message but it DOES NOT execute SQL Statements.
Note : Once you compiled the statement, DO NOT FORGET to turn NOEXEC off.