SQL SERVER – How to Validate Syntax and Not Execute Statement – An Unexplored Debugging Tip

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.

SQL SERVER - How to Validate Syntax and Not Execute Statement - An Unexplored Debugging Tip insertexec

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)

Previous Post
SQL SERVER – Identify Oldest Active Transaction with DBCC OPENTRAN
Next Post
SQL SERVER – SQLWays – Database and Application Migration Tool

Related Posts

No results found.

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.

    Reply
  • awesome trick I really like this

    Reply
  • 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

    Reply
  • What about running the estimated query plan to get a similar result :-)

    Reply
  • Eng.Motafa Elmasry
    January 20, 2014 4:03 pm

    The Actual Execution plan why not working with this option

    Reply
    • Vimal Kr. Prajapati
      January 22, 2014 5:42 pm

      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.

      Reply
  • James Anderson
    January 20, 2014 6:09 pm

    This is good for ad-hoc SQL but would you still use sp_refreshmodule for your stored procedures?

    Reply
  • Samurajevic Nindjicu
    January 20, 2014 11:35 pm

    Not working.

    Reply
  • We can use simply ctrl + L .

    Reply
  • Simply we can use CTRL + L

    Thanks
    Satish kumar

    Reply
  • 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

    Reply
  • thanks

    Reply
  • 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 ‘,’.

    Reply
  • Can this be used with a dynamically build query?

    Reply
    • 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;

      Reply
  • Syantax can be also validated by Parsing the SQL script. (Ctrl+F5)

    Reply
  • 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.

    Reply
  • Sorry typo there, missing between @@error and 0.

    Reply
  • Ravi Shankar Kota
    December 1, 2016 8:26 pm

    Hi Pinal,

    How it is different or effective than using Ctrl + L.Any suggestions.

    Reply
  • Hi i have to get an user defined error when the query is wrong

    Reply
  • Noexec checking only syntax not checking for table existence.can u please tell me command check for syntax and table in database

    Reply

Leave a Reply

Menu