You can’t use GO in dynamic SQL.

1

February 10, 2022 by Kenneth Fisher

This is one of those things that when I look back on it seems really obvious. Note: If at the end of this it isn’t overly obvious to you that’s ok too. I do a lot of dynamic SQL and GO is one of my favorite commands.

So here’s what happened. When I write dynamic SQL one of the steps I take is creating a script that works normally.

CREATE TABLE test (Id INT);
GO
CREATE VIEW vw_test AS 
	SELECT * FROM test;
GO

Next I declare a variable, put quotes around my command, and add sp_executesql. (If you are interested I’m actually skipping a few steps here. Such as incessant testing.)

DECLARE @sql nvarchar(max);
SET @sql = 'CREATE TABLE test (Id INT);
GO
CREATE VIEW vw_test AS 
	SELECT * FROM test;
GO'
EXEC sp_executesql @sql;
GO

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘GO’.
Msg 111, Level 15, State 1, Line 5
‘CREATE VIEW’ must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘GO’.

Well that wasn’t what I expected. I spent something like 20 minutes trying to figure out how to use GO in the silly command. Remember how I said this should have been obvious to me? Well .. if you go back and read the post on GO that I linked to above you’ll see that GO isn’t actually a T-SQL command. It’s a SQL Server Management Studio command (among other programs). It tells SSMS to run the bit above it separately from the bit below. (It’s a Batch Separator.) Which is why it won’t work in dynamic SQL that only runs T-SQL commands. So in the end, I wasn’t able to create the table and view in the same command. Not that it was a huge issue once I got past my hang-up.

DECLARE @sql nvarchar(max);
SET @sql = 'CREATE TABLE test (Id INT);'
EXEC sp_executesql @sql;

SET @sql = 'CREATE VIEW vw_test AS 
	SELECT * FROM test;'
EXEC sp_executesql @sql;
GO

One thought on “You can’t use GO in dynamic SQL.

  1. […] Kenneth Fisher can’t go in dynamic SQL and neither can you: […]

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,653 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: