CODEWITHSUNDEEP
sql-serversql-server-2012
user1589188
user1589188

Reputation: 5736

How to add CREATE DATABASE and CREATE SCHEMA inside a PROCEDURE?

Trying to make this work:

CREATE PROCEDURE createDbandSchema AS
BEGIN
    CREATE DATABASE MYDB
    USE MYDB
    CREATE SCHEMA MYSCHEMA
END

I got error

a USE database statement is not allowed in a procedure, function or trigger.

Upvotes: 2

Views: 1419

Answers (2)

Andomar
Andomar

Reputation: 238246

The create schema statement must be the first in the batch, so it requires a nested scope. The outer scope changes to the new database. The inner scope runs create schema as the first statement of the batch:

create procedure createDbandSchema(@db sysname, @schema sysname)
as begin
    declare @sql nvarchar(max) = N'CREATE DATABASE ' + quotename(@db);
    exec (@sql)
    set @sql = N'USE ' + quotename(@db) + '; exec (N''CREATE SCHEMA ' +
        quotename(@schema) + N''')';
    exec (@sql)
end

Upvotes: 2

Sanjay kumar
Sanjay kumar

Reputation: 1673

Use the CREATE SCHEMA syntax or, in SSMS, drill down through Databases -> YourDatabaseName -> Security -> Schemas. Right-click on the Schemas folder and select "New Schema..."

enter image description here

https://msdn.microsoft.com/en-us/library/dd207005.aspx

http://www.c-sharpcorner.com/UploadFile/63f5c2/schema-in-sql-server-2012/

Upvotes: 0

Related Questions