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