How to create a table inside of recently created database with dynamic sql?

Question

I want to populate a set of tables and procedures into new databases programmatically.

Then I prepared a Initialize Script and I will use it inside a procedure who must created new databases.

I tried with a simple example and didn't work:

CREATE PROCEDURE PROCEDURETOREPLICATECOMMONSCHEMA
    @databaseName NVARCHAR(40)
AS
BEGIN
    DECLARE @sqlCreation NVARCHAR(MAX);
    SET @sqlCreation = '
        USE MASTER;

        EXEC(''CREATE DATABASE ' + @databaseName + ''');

        EXEC(''USE ' + @databaseName + ''');

        CREATE TABLE Testing
        (
            TestPk int,
            TestDescription nvarchar(80)
        );
    ';

    PRINT @sqlCreation;
    EXEC sp_executesql @sqlCreation;
END
GO

When I execute this procedure, it creates the table Testing inside master database instead of TestDatabase1.

EXEC PROCEDURETOREPLICATECOMMONSCHEMA 'TestDatabase1'
GO

Answer 1

Please make use of the below code. Its working fine with SQL Server 2012.

 CREATE PROCEDURE PROCEDURETOREPLICATECOMMONSCHEMA
    @databaseName NVARCHAR(40)
AS
BEGIN
   BEGIN
    EXEC('USE MASTER;CREATE DATABASE ' + @databaseName + ';')
    EXEC('CREATE TABLE ' + @databaseName + '.dbo.Testing(TestPk int,TestDescription nvarchar(80));')
END
END
GO

Answer 2

First create the database, then create the table (two separate statements):

CREATE PROCEDURE PROCEDURETOREPLICATECOMMONSCHEMA
    @databaseName NVARCHAR(40)
AS
BEGIN
    DECLARE @sqlCreation NVARCHAR(MAX);
    SET @sqlCreation = 'USE MASTER;
                        EXEC(''CREATE DATABASE ' + @databaseName + ''');';

    EXEC sp_executesql @sqlCreation;   

    SET @sqlCreation = 'EXEC(''USE ' + @databaseName + ''');

        CREATE TABLE ' + @databaseName + '.dbo.Testing
        (
            TestPk int,
            TestDescription nvarchar(80)
        );';

    EXEC sp_executesql @sqlCreation;
END
GO