SQL Server create database dynamically and use the database in the script

DECLARE @DatabaseNamePart VARCHAR(50) = 'Part1'
DECLARE @DataBaseName VARCHAR(70) = 'MyDataBase_' + @DatabaseNamePart

IF NOT EXISTS (SELECT * FROM master..sysdatabases WHERE [name] = @DataBaseName) BEGIN
    DECLARE @SqlQuery NVARCHAR(150) = 'CREATE DATABASE ' + @DataBaseName
    EXECUTE (@SqlQuery)     
END



-- code edited - declaring a new variable and setting the dbname

Declare @newDBName VARCHAR(30)

SET @DBName = @DataBaseName

exec('USE '+ @DBName)

-- code edited


--Create tables query

IF NOT EXISTS (SELECT * FROM sysobjects WHERE [name]='MyTable' AND xtype='U') BEGIN
    CREATE TABLE MyTable (
        [id] INT PRIMARY KEY NOT NULL IDENTITY,
        [Name] VARCHAR(150) NOT NULL,
    )
END

One possible solution to your problem is handling this server side.

• You would use a connection string pointing to master database. This connection string will be used to create the new db (let's call it Fruits)
• You would use another connection string pointing to the newly created db (i.e. a connection string pointing to e.g. Fruits)

Use the following as a guiding example:

1. You call the method below to create the Database:

public async Task CreateDatabase(string databaseName)
    {
        var query = @"CREATE DATABASE [Xy] CONTAINMENT = NONE ON  PRIMARY 
        ( NAME = N'Xy', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Xy.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
        LOG ON 
        ( NAME = N'Xy_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Xy_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
        WITH LEDGER = OFF".Replace("Xy", databaseName);
        
        try
        {
            var connection = _databaseConnector.GetSqlConnectionMasterDatabase();
            await connection.OpenAsync();
            var command = new SqlCommand(query, connection);
            await command.ExecuteNonQueryAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError("Db failure for {@Operation}! {@Exception}", nameof(CreateDatabase), ex);
        }
    }

2. You call the needed operation on the db that you just created (e.g the Fruits db)

public async Task CreateSomeTable(string databaseName)
    {
        const string query = "CREATE TABLE Pineapple (PineappleID BIGINT NOT NULL, DateOfBirth datetime2 null)";

        try
        {
            var connection = _databaseConnector.GetSqlConnectionCustomDatabase(databaseName);
            await connection.OpenAsync();
            var command = new SqlCommand(query, connection);
            await command.ExecuteNonQueryAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError("Db failure for {@Operation}! {@Exception}", nameof(CreateSomeTable), ex);
        }
    }

3. How do you define a custom connection i.e. pointing to a custom database ? See below

 public SqlConnection GetSqlConnectionCustomDatabase(string customDatabase)
    {
        return new SqlConnection(ConnectionStringForMasterDatabase.Replace("master", customDatabase));
    }

4. Example of calling code: (where _myRepository provides the implementations above)

const string dbName = "Fruits";
await _myRepository.CreateDatabase(dbName);
await _myRepository.CreateSomeTable(dbName);