2

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.

Simon Morgan
  • 2,018
  • 5
  • 23
  • 36
  • 1
    `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 Dec 03 '19 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 Dec 03 '19 at 15:14
  • 1
    From what tool/application are you trying to create the database? – Piotr Palka Dec 03 '19 at 15:31
  • @Piotr: Using `SqlCommand` in C#. – Simon Morgan Dec 03 '19 at 15:37

2 Answers2

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
1

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

  1. Open connection to master.
  2. Create new database (just create database statement).
  3. Instead of USE call SqlConnection.ChangeDatabase(String) Method
  4. Execute remaining batches
Piotr Palka
  • 3,086
  • 1
  • 9
  • 17