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:
USE AdventureWorks2012
GO
-- Change Setting of NoEXEC to ON
SET NOEXEC ON;
-- INSERT Statement
INSERT INTO HumanResources.Shift
(Name,StartTime, EndTime,ModifiedDate)
VALUES ('Pinal', GETDATE(), GETDATE()+1, GETDATE());
-- Change Setting of NoEXEC to OFF
SET NOEXEC OFF;
GO
-- Check Table Data
SELECT *
FROM HumanResources.Shift;
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 (https://blog.sqlauthority.com)
24 Comments. Leave new
We are not getting any error message while putting wrong syntex after setting SET NOEXEC ON
use msdb
GO
set noexec on
select * from restorehistory_xxx —– We replaced the table name “restorehistory” with wrong name “restorehistory_xxx”
Output
———————————————————————–
Command(s) completed successfully.
Because that is not a syntax error..
Yes, correct. For that you can use SET FMTONLY ON; and it will validate everything on your SQL syntax without executing it.
awesome trick I really like this
Thanks for the tip Pinal.
I was wondering how it is different from Parse functionality of SSMS. I believe that we could achieve same from Parse as well.
Regards,
Ashish Jain
What about running the estimated query plan to get a similar result :-)
The Actual Execution plan why not working with this option
As we all know SQL query execution takes place in two phases; one is Compilation and the other one is Execution.Because after setting this option as “ON” SQL statements will not get executed, Instead it will only get compiled.
This is good for ad-hoc SQL but would you still use sp_refreshmodule for your stored procedures?
Not working.
We can use simply ctrl + L .
Simply we can use CTRL + L
Thanks
Satish kumar
Thanks Pinal, learned something new.
Would another viable solution be to place in a transaction and rollback?
BEGIN TRAN AAA
INSERT INTO Users (UserName, CustId, Permission)
VALUES (‘AAA’, 5, 0)
ROLLBACK TRAN AAA
thanks
ROUND(([Qry_Purchase_Payment].[Balance] – (ISNULL(SUM([Debit_Note].[Amount],
0))))), 2) AS Balance, (ISNULL(SUM([Debit_Note].[Amount], 0))))
error in line 1: incorrect syntax near the ‘,’.
Can this be used with a dynamically build query?
I noticed that when I used NOEXEC, I couldn’t build a query dynamically, but I was able to validate the syntax using SET PARSEONLY ON;
Syantax can be also validated by Parsing the SQL script. (Ctrl+F5)
Thank you Pinal, always enjoy your articles, keep up the excellent work.
I recently saw an example of some auto-generated code using NOEXEC in an interesting way, after each statement it would have “IF @@ERROR 0 SET NOEXEC ON”.
I thought that was a cool way to tumble out of a script if any statement failed; typically I’ve used GOTO with something like ENDOFSCRIPT: label.
Sorry typo there, missing between @@error and 0.
Hi Pinal,
How it is different or effective than using Ctrl + L.Any suggestions.
Hi i have to get an user defined error when the query is wrong
Noexec checking only syntax not checking for table existence.can u please tell me command check for syntax and table in database