Blog Post

You can’t use GO in dynamic SQL.

,

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

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating