How to Validate SQL Syntax and Not Execute Statement?

What is the surest way to check that your syntax is valid and will work with SQL Server?

Solution:
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.

Use test_8t_FEB

-- Change Setting of NoEXEC to ON

SET NOEXEC ON;

-- INSERT Statement

INSERT INTO [test_8t_FEB].[dbo].[like]

           ([months]

           ,[count])

     VALUES

           ('nov',

           120)

GO

-- Change Setting of NoEXEC to OFF

SET NOEXEC OFF;

GO

-- Check Table Data

SELECT *

FROM [test_8t_FEB].[dbo].[like];

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