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.

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)

About these ads

14 thoughts on “SQL SERVER – How to Validate Syntax and Not Execute Statement – An Unexplored Debugging Tip

  1. 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.

  2. 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

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

  3. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s