In TSQL, does the SET command must in it's own batch?

Question

Hi guys,

I'm facing a problem now. I use ruby and SQLCMD to generate some TSQL scripts. Now I want to check the syntax of the generated script. I use the following SQL:

SET PARSEONLY ON;
SELECT 888
SET PARSEONLY OFF;

I test it in SSMS, when you select these three statements as a whole batch, sql server will give me the result, which is 888. I don't want the actual result, I just want to test whether the syntax of my script is right. So I tried the following SQL:

SET PARSEONLY ON;
GO
SELECT 888
GO
SET PARSEONLY OFF;
GO

Now if I select all these statements as a whole batch, SQL Server will just tell me Command(s) completed successfully. So what I want to know is whether the SET command should in it's own batch?

Answer 1

Yousui, No what you are doing is almost right; Read here for more info. Just one thing: you cannot use SET PARSEONLY ON and SET PARSEONLY OFF in the same statement because the query would be executed and not parsed. So try this:

SET PARSEONLY ON
*-- YOUR QUERY*
GO
SET PARSEONLY OFF 

Just remember that PARSEONLY only checks the syntax, not the objects, like tables etc. If you want to do object validation and checks use NOEXEC;

SET NOEXEC ON GO
*-- YOUR QUERY HERE* 
SET NOEXEC OFF