Stored procedure in Dynamic DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

msnews.microsoft.com - 26 Jul 2005 18:39 GMT

I want to create the procedure in my dynamic database like that but it's not
working... can any one add their idea regarding this... Thanks
DECLARE @SQLSTATEMENT VARCHAR(200)
SET @SQLSTATEMENT = 'CREATE PROCEDURE '+@DBName+'.dbo.Portal_AddEvent
(
@ModuleID int,
@UserName nvarchar(100),
@Title nvarchar(100),
@ExpireDate DateTime,
@Description nvarchar(2000),
@WhereWhen nvarchar(100),
@ItemID int OUTPUT
)'

EXEC (@SQLSTATEMENT)

Reply to this Message

Jack - 26 Jul 2005 19:08 GMT

DECLARE @SQLSTATEMENT VARCHAR(2000)

SET @SQLSTATEMENT = 'USE ' + @dbname
+ ' EXEC sp_executesql N'' CREATE PROCEDURE dbo.Portal_AddEvent
(
@ModuleID int,
@UserName nvarchar(100),
@Title nvarchar(100),
@ExpireDate DateTime,
@Description nvarchar(2000),
@WhereWhen nvarchar(100),
@ItemID int OUTPUT
) '''

EXEC (@sqlstatement)

> I want to create the procedure in my dynamic database like that but it's not
> working... can any one add their idea regarding this... Thanks
[quoted text clipped - 11 lines]
>
> EXEC (@SQLSTATEMENT)

Reply to this Message

msnews.microsoft.com - 26 Jul 2005 19:25 GMT

DECLARE @SQLSTATEMENT VARCHAR(2000)
DECLARE @dbname VARCHAR(20)

SET @dbname = 'Portal'

SET @SQLSTATEMENT = 'USE '+ @dbname + ' EXEC sp_executesql CREATE PROCEDURE
dbo.Portal_AddEvent
(
@ModuleID int,
@UserName nvarchar(100),
@Title nvarchar(100),
@ExpireDate DateTime,
@Description nvarchar(2000),
@WhereWhen nvarchar(100),
@ItemID int OUTPUT
) '

EXEC (@sqlstatement)

Error:-
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.

> DECLARE @SQLSTATEMENT VARCHAR(2000)
>
[quoted text clipped - 28 lines]
>>
>> EXEC (@SQLSTATEMENT)

Reply to this Message

msnews.microsoft.com - 26 Jul 2005 21:44 GMT

Can any one help?
DECLARE @SQLSTATEMENT VARCHAR(2000)
DECLARE @dbname VARCHAR(20)

SET @dbname = 'Portal'

SET @SQLSTATEMENT = 'USE '+ @dbname + ' EXEC sp_executesql CREATE PROCEDURE
dbo.Portal_AddEvent
(
@ModuleID int,
@UserName nvarchar(100),
@Title nvarchar(100),
@ExpireDate DateTime,
@Description nvarchar(2000),
@WhereWhen nvarchar(100),
@ItemID int OUTPUT
) '

EXEC (@sqlstatement)

Error:-
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.

> DECLARE @SQLSTATEMENT VARCHAR(2000)
> DECLARE @dbname VARCHAR(20)
[quoted text clipped - 51 lines]
>>>
>>> EXEC (@SQLSTATEMENT)

Reply to this Message

David Gugick - 27 Jul 2005 01:32 GMT

> I want to create the procedure in my dynamic database like that but
> it's not working... can any one add their idea regarding this...
[quoted text clipped - 11 lines]
>
> EXEC (@SQLSTATEMENT)

What happens when you PRINT the @SQLSTATEMENT. Can you then create the
procedure in Query Analyzer? I don't see any body text for the
procedure. What error are you getting? What about "Portal AddEvent". Is
that a space or an underscore in the procedure name (it's not clear from
my newsreader).

Signature

David Gugick
Quest Software
www.imceda.com
www.quest.com

Reply to this Message

Roy - 28 Jul 2005 03:59 GMT

David,

First of all thanks for your reply, After print query analyzer gave me the
following ....

DECLARE
@SQLSTATEMENT VARCHAR(1000),
@DBName VARCHAR(20)

SET @DBName = 'Portal'
SET @SQLSTATEMENT = 'CREATE PROCEDURE'+DBName+'.dbo.Portal_AddEvent
(
@ModuleID int,
@UserName nvarchar(100),
@Title nvarchar(100),
@ExpireDate DateTime,
@Description nvarchar(2000),
@WhereWhen nvarchar(100),
@ItemID int OUTPUT
)'

PRINT (@SQLSTATEMENT)

CREATE PROCEDURE Portal.dbo.Portal_AddEvent
(
@ModuleID int,
@UserName nvarchar(100),
@Title nvarchar(100),
@ExpireDate DateTime,
@Description nvarchar(2000),
@WhereWhen nvarchar(100),
@ItemID int OUTPUT
)

After Execution it gave me the following error.

Server: Msg 166, Level 15, State 1, Line 2
'CREATE PROCEDURE' does not allow specifying the database name as a prefix
to the object name.

Waiting for your reply

Thanks

> > I want to create the procedure in my dynamic database like that but
> > it's not working... can any one add their idea regarding this...
[quoted text clipped - 23 lines]
> www.imceda.com
> www.quest.com

Reply to this Message

David Gugick - 28 Jul 2005 05:45 GMT

> PRINT (@SQLSTATEMENT)
>
[quoted text clipped - 16 lines]
>
> Waiting for your reply

I think that pretty much says it: No database name allowed in the create
statement for a stored procedure. Even so, you don't have a stored
procedure in that PRINT statement anyway.

So, you need to be in the database first before you can create the
procedure. You can use a USE statement in the EXEC because SQL Server
will bark at you that CREATE PROC needs to be the first statement in the
batch.

Signature

David Gugick
Quest Software
www.imceda.com
www.quest.com

Reply to this Message

msnews.microsoft.com - 28 Jul 2005 15:27 GMT

Yes David, that's what I am asking you.... I am creating dynamic database
and after creating the database I want to create procedures in it ....
database created successfully and records have been inserted successfully
but when I try to create Stored Procedure then there is giving me an error
like I should in that database .... so the question is that can I do this
dynamically?

Thanks

>> PRINT (@SQLSTATEMENT)
>>
[quoted text clipped - 24 lines]
> procedure. You can use a USE statement in the EXEC because SQL Server will
> bark at you that CREATE PROC needs to be the first statement in the batch.

Reply to this Message

David Gugick - 28 Jul 2005 17:01 GMT

> Yes David, that's what I am asking you.... I am creating dynamic
> database and after creating the database I want to create procedures
> in it .... database created successfully and records have been
> inserted successfully but when I try to create Stored Procedure then
> there is giving me an error like I should in that database .... so
> the question is that can I do this dynamically?

You can, but as I stated, you need to _be_ in that database first. So
create the database and then change context to it using USE [New
Database Name] and then create the procedures. I can't tell from your
post whether or not this is run as a single script or there is a client
application at work here. If you have an application driving the
creation of the database, then do this:

- Connect to the server
- Execute the CREATE DATABASE script
- Change context to the new database using a USE statement
- Execute the scripts to create the stored procedures and leave off the
database name

Signature

David Gugick
Quest Software
www.imceda.com
www.quest.com

Reply to this Message

Roy - 06 Aug 2005 03:29 GMT

You are right and the sql server suppose to do this but it can't do this
.... I have created the database dynamically and after I used USE command
and then Created all the stored procedures after but it was giving me an an
error which I stated in earlier email.

Thanks

> > Yes David, that's what I am asking you.... I am creating dynamic
> > database and after creating the database I want to create procedures
[quoted text clipped - 21 lines]
> www.imceda.com
> www.quest.com

Reply to this Message

dtsuser - 09 Aug 2005 20:23 GMT

there are 2 changes.
1. note I am using N in front of the quote.
2. After the proc variables and closing paranthesis, you need to say 'as'

DECLARE @dbname nVARCHAR(20)
exec (N'USE PORTAL')

DECLARE @sqlstatement nVARCHAR(2000)
SET @sqlstatement = 'CREATE PROCEDURE dbo.usp_test1 ( @ModuleID int
,
@UserName nvarchar(100),
@Title nvarchar(100),
@ExpireDate DateTime,
@Description nvarchar(2000),
@WhereWhen nvarchar(100)
,@ItemID int OUTPUT
) as '
exec ( @sqlstatement )

Reply to this Message