0

I am trying to write a code like this in T-SQL

BEGIN TRANSACTION

CREATE DATABASE DBName

USE DBName

BEGIN TRY
    --Some sample query that could go wrong
END TRY
BEGIN CATCH
    ROLLBACK
END CATCH

I faced the error

DBName Database doesnt exist

This answer advised to use GO keyword (Click Here)

I faced the error

Syntax error near 'GO'

This answer tells that GO can't be used in T-SQL (Click Here)

I am using C# OleDBConnection to execute the SQL code

It works if I separately execute CREATE DATABASE first and then execute USE DBName in a separate file.

But in that case ROLLBACK would not rollback the database creation command and I will be left with a blank database. That is why, I need to include both CREATE DATABASE and USE DATABASE commands in the same file

How can I use something which is equivalent to GO keyword in T-SQL ?

Lelouch
  • 37
  • 4
  • The problem you are having it you are trying to create the database in the same batch you switch context to it. As such the statement fails as when it is parsed, the database, `DBName` doesn't exist. In an IDE, you would separate the batches with `GO`, but if you aren't using an `IDE` you will need to create the database in a separate batches programmatically. – Thom A Aug 19 '21 at 16:07
  • @Larnu In that case, how can I execute ROLLBACK such that Database also gets dropped? When I use it now, only commands of that batch is rolling back – Lelouch Aug 19 '21 at 16:10
  • You'd need to catch the error in your application, and the issue a `DROP`. – Thom A Aug 19 '21 at 16:12
  • 1
    *"How can I use something which is equivalent to GO keyword in T-SQL ?"* `GO` isn't a T-SQL keyword either. This is very specifically stated in the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver15#remarks). – Thom A Aug 19 '21 at 16:29
  • What is the overarching goal here and why the need to also drop the database? If you are re-running the batch multiple times then just create the database if it doesn't exist or have a separate clean-up process. – Stu Aug 19 '21 at 16:32

1 Answers1

1

Handle the rollback yourself.

  • first create database in one batch
  • next use database in another batch
  • If you decide that you no longer want the database
    • drop database
Brian Z
  • 343
  • 1
  • 8