CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Sparky
Sparky

Reputation: 763

Defining a schema inside a procedure

I'm working on creating a model data base design for a retail store. I'm trying to create a single procedure which will initialize the database schema.

What I'm trying to achieve is to create a new schema from inside the procedure. My code is as follows:

begin trans

create procedure Retail_Fill
as
    create schema Retail_Test;  
    go
    create table Retail_Test.customer(
        cust_id int,
        cust_name varchar(30),
        cust_phone int,
        cust_add varchar(50),
        constraint pk_customer primary key (cust_id)
    );

Here the create schema statement works fine by itself. But inside the procedure it gives an error:

Invaid Syntax!CREATE SCHEMA must be the only statement in the batch

I want to know if it is at all possible to achieve this. If yes then what am I doing wrong or where is the error?

Upvotes: 2

Views: 286

Answers (2)

Prabhat
Prabhat

Reputation: 4264

As Mikael Eriksson said above creating DB Schema should be in a single script file or may be you can configure some deployment that will create your schema. So it will be good to avoid creating schema within procedures.

You can have these inside procedure when you have to design the schema on fly for each customer (for example).

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

CREATE SCHEMA has to be executed as a separate batch. Batches in SQL Server Management Studio is separated by GO. That is not the case in a stored procedure. You can do what you want by using EXECUTE for the statements that needs to be in a batch of its own like CREATE SCHEMA or CREATE PROCEDURE.

create procedure Retail_Fill
as
exec('create schema Retail_Test');

create table Retail_Test.customer(
    cust_id int,
    cust_name varchar(30),
    cust_phone int,
    cust_add varchar(50),
    constraint pk_customer primary key (cust_id)
);

Upvotes: 4

Related Questions