Using "Use" in stored procedure

Question

Post reply

Using "Use" in stored procedure

Chow's

SSC Veteran

Points: 296
More actions
December 13, 2010 at 8:13 am

#232455

Hii all, my requirement is in a stored procedure I have to create a database dynamically and add some objects to the newely created database. Am successfull in 1st part but am not able to use that database in the same procedure for creating some db objects.
When I try "Use databasename" it is throwing some error.
Can any one help me in this regard.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Resender SSCertifiable Points: 7443 More actions · Answer 1

Can you provide the error

What objects do you want to create

If you want to create tables then you could do it like this

declare @useBase nvarchar(max)

set @useBase = 'create table Test.dbo.Test(ID int,VarID nvarchar(max))'

exec sp_executesql @useBase

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I believe that you would need to use dynamic SQL, to create the database.

declare @cmd varchar(max)

select @cmd = ' create table ' + @newdb + '.dbo.mytable (myid int)'

exec (@cmd)

Chow's SSC Veteran Points: 296 More actions · Answer 3

Actually this is wat am trying to do

CREATE PROCEDURE AAA

AS

BEGIN

CREATE DATABASE DB_USERS;

USE DB_USERS;

CREATE TABLE ADMINUSER (ID INT,UNAME VARCHAR(40),PWD VARCHAR(40));

--IN THE SAME WAY LOT OF TABLES AND PROCEDURES

END

and am getting the following error

Msg 154, Level 15, State 1, Procedure AAA, Line 6

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

You cannot do this. You would either need to create the tables with the proper database name as I've done with dynamic SQL, or run a pre-written script. If the table names are known, you can then easily load that script and run if from something like SQLCMD or as an SSIS package, with the proper database context.

A stored procedure is built to be a method of code execution, not as a container for creating other objects. Certain creation statements need to be in their own batch, which you cannot do in a stored procedure.