Madhivanan is SQL Server MVP and very talented SQL expert. Here is one of the nugget he shared on Just Learned.
He shared a tip where there were two interesting point to learn.
- Do not use keywords as an object name
- [read DHall’s excellent comment below]
He has given excellent example how GO can be executed as stored procedure. Here is the extension of the tip. Create a small table and now just hit EXEC GO; and you will notice that there is row in the table.
Create Stored Procedure
CREATE PROCEDURE GO AS SELECT 1 AS NUMBER
Create Table
CREATE TABLE T1 (ID INT)
Now execute following code
INSERT INTO T1(ID) EXEC GO;
Now when selecting from table it will give us following result:
SELECT * FROM T1
Now see following resultset:
So without inserting any data we inserted the data, well indeed good puzzle but very bad practical practice. Every body should be aware of this gotcha and avoid it. Thanks Madhivanan for teaching this interesting learning.
Republishing here with authorization of Just Learned.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
The second point you make isn’t exactly accurate. The semicolon has nothing (or very little) to do with executing the GO stored procedure. The code works the same even without the semicolon as long as EXEC and GO are on the same line or if [GO] is wrapped in square brackets.
However, if the GO proc name is the only thing (other than an optional comment) on the line following following the EXEC command, then the SQL query window will try to interpret it as a batch delimiter if it’s not followed by a semicolon or wrapped in square braces.
In other words, the following SQL will work as expected
INSERT INTO T1(ID) exec GO — Note no semicolon
SELECT * FROM T1
— and so will this:
INSERT INTO T1(ID) exec
GO; — Note the semicolon
SELECT * FROM T1
— and so will this:
INSERT INTO T1(ID) exec
[GO] — Note the square brackets but no semicolon
SELECT * FROM T1
— but this won’t:
INSERT INTO T1(ID) exec
GO — Note no semicolon
SELECT * FROM T1
Another evidence that GO is different from SQL reserved word is to duplicate your experiment using [TABLE] as the proc name instead of GO. You’ll note that unlike the word GO, the word TABLE must be wrapped in square brackets so that TSQL will allow it to be an object name. For GO, the square brackets are optional.
— The following code works fine
CREATE PROCEDURE [TABLE]
AS SELECT 1 AS NUMBER
GO
INSERT INTO T1(ID) exec [TABLE] — Note the square brackets
SELECT * FROM T1
— but this generates a syntax error
CREATE PROCEDURE [TABLE]
AS SELECT 1 AS NUMBER
GO
INSERT INTO T1(ID) exec TABLE — Note no square brackets
SELECT * FROM T1
As you mention in your first point, using keywords in this manner can be trouble, and should generally be avoided for many reasons, but it especially helps in this case to understand that GO is not a reserved word in TSQL, but only in some of the tools (like SSMS) that process SQL queries in batches.
The following excerpt from SQL BOL should help clarify the difference between the GO batch delimiter and true SQL reserved words.
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
Awesome Gotcha! Thanks for the repost.
Hi pinal sir,
thanks for the updates..
Useful and informative article..
Regard$
Chirag Satasiya
Really a nice observation as well fact.
Pinal , can we have an article on debuggin T-SQL in SSMS.
I am googling on it , but couldn’t get any simpler article. I anticipate your’s one will be of great help.
-Lalit
Hi Pinal Sir,
Very Informative post.
Thanks to you and DHall
You can change the batch delimiter used by SSMS from “GO” to “xyzzy”, or “8”, or pretty much anything you want. Jonathan @fatherjack Allen did a demo at SQLBits recently showing some of the weirdness that can happen if you set it to a number…
In SSMS, go to “Tools”, “option”, “Query Execution”, “SQL Server”. And there you have the option to change the batch separator. See https://docs.microsoft.com/en-us/sql/ssms/scripting/query-and-text-editors-sql-server-management-studio?view=sql-server-2017