T-SQL equivalent of GO

Question

I'm trying to write a T-SQL script to create a database and the corresponding tables. I'm having a problem where the USE statement complains that the database that I just "created" doesn't exist. If I run the script within SQL Server Management Studio so that I can make use of the GO statement, I don't get this issue.

Is there a T-SQL equivalent of GO that I can use to make sure the CREATE DATABASE gets executed before the USE?

I've tried BEGIN/COMMIT TRANSACTION and BEGIN/END but they didn't help.

GO is a batch separator in SSMS/sqlcmd/etc; there is no equivalent in T-SQL as it is the application that separates the batches. If you need to "emulate" the GO operator simply send multiple batches from your application. — Thom A
– Thom A ♦, Commented Dec 3, 2019 at 15:14
There's not really any such thing, but EXEC comes close (EXEC ('CREATE DATABASE Bar')). Not all statements are allowed in a dynamic batch, however, nor are transactions supported for everything (that includes, in particular, database creation). Real, separate command batches that don't depend on the ambient context aren't possible in pure T-SQL alone, requiring complex workarounds like creating and running throwaway agent jobs. — Jeroen Mostert
– Jeroen Mostert, Commented Dec 3, 2019 at 15:14
From what tool/application are you trying to create the database? — Piotr Palka
– Piotr Palka, Commented Dec 3, 2019 at 15:31

David Browne - Microsoft · Accepted Answer · 2019-12-03 15:33:08Z

2

Is there a T-SQL equivalent of GO that I can use to make sure the CREATE DATABASE gets executed before the USE?

Yes. Dynamic SQL. Each dynamic SQL invocation is a parsed, compiled, and executed as a separate batch.

EG:

exec ('
create database foo
 ')
exec ('
use foo
create table bar(id int)
')

Note that when used in dynamic SQL use database only change the database context for the dynamic batch. When control returns to the calling batch, the database context is restored.

edited Dec 3, 2019 at 15:33

answered Dec 3, 2019 at 15:14

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Piotr Palka · Accepted Answer · 2019-12-03 15:58:04Z

1

In C# you should use separate calls to SqlComand for each batch.
High level steps.

Open connection to master.
Create new database (just create database statement).
Instead of USE call SqlConnection.ChangeDatabase(String) Method
Execute remaining batches

answered Dec 3, 2019 at 15:58

Piotr Palka

3,2351 gold badge12 silver badges20 bronze badges

Collectives™ on Stack Overflow

T-SQL equivalent of GO

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related