In TSQL, does the SET command must in it's own batch?
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?
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
精彩评论