CODEWITHSUNDEEP
sqlsql-serversql-server-2008
davor
davor

Reputation: 969

Creating database, tables, procedures using stored procedure/function?

I need to create database (+schema, data, procedures, roles) using procedure/function - when I call it from c# code.

Situation:

I have masterDB database, in which user can create new company. When he creates new company the new database (for example: nike2011) should be created (ddl script: tables + stored procedures would be stored somewhere - in procedure/script?).

If he chooses to copy catalogs from some other database (ex. nike2010, or cocacola2009), the data should be copied from chosen database to database nike2011.

After creating db, I need to grant all sql server users to newly created database.

I tried using stored procedure, but I can't create stored procedure inside of stored procedure for creating new database, and I can't use 'use nike2010' in stored procedure.

Hope you understand.

Upvotes: 1

Views: 2220

Answers (1)

gonsalu
gonsalu

Reputation: 3184

You'll have to execute dynamic SQL (SQL constructed using strings) using sp_executesql, for example.

Upvotes: 2

Related Questions