CODEWITHSUNDEEP
sqlsql-server
Sudr
Sudr

Reputation: 15

How to create a schema inside a database (database and schema should be dynamic)

I tried the following SQL code:

DECLARE @dbname VARCHAR(40);
DECLARE @CreateSchema NVARCHAR(MAX);

SELECT @dbname = 'abc';
SELECT @Schema = 'sdssd';

SELECT @CreateSchema = N'USE ' + @dbname + '; CREATE SCHEMA ' + @Schema + ' AUTHORIZATION [dbo]' 

EXEC(@CreateSchema);

This is results in an error:

Create Schema must be the first statement in a query batch

Can anyone help me?

Upvotes: 0

Views: 182

Answers (1)

Bhavin Gosai
Bhavin Gosai

Reputation: 211

You cannot get away with this directly. Though, if you are open to use nested EXEC, try following. 

Declare @Schema varchar(40);
Declare @dbname varchar(40);
Declare @CreateSchema nvarchar(Max);
SELECT  @dbname = 'abc';
SELECT @Schema = 'sdssd';

SELECT @CreateSchema = N'USE '+@dbname+'; EXEC(''CREATE SCHEMA '+ @Schema +' AUTHORIZATION [dbo]'')' 
EXEC(@CreateSchema);

FYI - you missed declaring a variable @Schema in your sample.

Upvotes: 2

Related Questions