In one of the recent interview I had asked questions of interviewees – If you are a SQL Server Developer there must be times when you want to validate syntax, but do not want to execute a query.
What is the surest way to check that your syntax is valid and will work with SQL Server?
I often get answers that intelligence in SQL Server will automatically let us know or we should try this out on a development server or we should attempt to create stored procedure etc. Honestly, none of the answer is accurate. They are all workaround which works sometime and which does not work sometime.
Here is how you can do it.
You can set the context of your execute to On or Off with the help of NOEXE setting. Let me explain you with the help of AdventureWorks Database and setting NOEXEC.
First look at the following query:
-- Change Setting of NoEXEC to ON
SET NOEXEC ON;
-- INSERT Statement
INSERT INTO HumanResources.Shift
VALUES ('Pinal', GETDATE(), GETDATE()+1, GETDATE());
-- Change Setting of NoEXEC to OFF
SET NOEXEC OFF;
-- Check Table Data
Here is the result of the query when we executed.
Even though we have an INSERT statement right before SELECT statement, there is no impact of the INSERT statement because we have executed SET NOEXEC ON before the INSERT. When Setting NOEXEC is set to ON, it will validate the syntax of the statement but will not execute it. If there is an error it will display the error on the screen. Now try to change the name of the table or anything in the above statement and it will throw an error.
Please do not forget to set the value of NOEXEC statement to OFF right after your test or otherwise all of your statements will not execute on SQL Server.
Now when you are debugging and see any syntax which is part of large query and you want to validate it, you can just do this with about Syntax. If you know similar cool tip, which you think I should share on the blog, please leave a comment and I will post on the blog with due credit.
Reference: Pinal Dave (http://blog.sqlauthority.com)