creating a stored procedure using a stored procedure

[Database Administrator]

Is is not possible to use one stored procedure to create another in a
different database?

Server: Msg 111, Level 15, State 1, Procedure CreateProc, Line 9
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

I thought ";" was enough to separate the batch? Any workaround, or is this a
lost cause?

Below is my failed attempt...

Paul

------------

CREATE PROCEDURE [dbo].[CreateProc]
@Database varchar(100),
@GUID uniqueidentifier,
@Comments varchar(8000)
AS

DECLARE @strGUID varchar(50)
SET @strGUID = CAST(@GUID as varchar(50))

SET @Comments = REPLACE(@Comments, '''', '')

DECLARE @operation varchar(30)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' +
@Database + '][dbo].[' + @strGUID + ']') AND type in (N'P', N'PC'))
BEGIN
SET @operation = 'ALTER'
--TODO: comment-updating feature (this will involve some parsing and text
replacement)
-- right now we won't mess with any existing content
RETURN
END
ELSE
BEGIN
SET @operation = 'CREATE'
END

DECLARE @SQL varchar(8000)
SET @SQL = '
USE <@DATABASE>
;
<@OPERATION@> PROCEDURE [dbo].[<@SPNAME@>]
AS
/*
<@COMMENTS@>
*/
PRINT ''NEW STORED PROCEDURE''
'

SET @SQL = REPLACE(@SQL, '<@DATABASE>', @Database)
SET @SQL = REPLACE(@SQL, '<@OPERATION@>', @operation)
SET @SQL = REPLACE(@SQL, '<@SPNAME@>', @strGUID)
SET @SQL = REPLACE(@SQL, '<@COMMENTS@>', @Comments)

PRINT 'Executing generated SQL:
' + @SQL
Execute(@SQL);

[Database Administrator]

If the CREATE or ALTER were run as dynamic SQL it would constitute its
own batch, and I believe it would work from within the outer
procedure.

Roy Harvey
Beacon Falls, CT

On Tue, 5 Jun 2007 14:24:10 -0400, "PJ6" wrote:

>Is is not possible to use one stored procedure to create another in a
>different database?
>
>Server: Msg 111, Level 15, State 1, Procedure CreateProc, Line 9
>'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
>
>I thought ";" was enough to separate the batch? Any workaround, or is this a
>lost cause?
>
>Below is my failed attempt...
>
>Paul
>
>------------
>
>CREATE PROCEDURE [dbo].[CreateProc]
>@Database varchar(100),
>@GUID uniqueidentifier,
>@Comments varchar(8000)
>AS
>
>DECLARE @strGUID varchar(50)
>SET @strGUID = CAST(@GUID as varchar(50))
>
>SET @Comments = REPLACE(@Comments, '''', '')
>
>DECLARE @operation varchar(30)
>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' +
>@Database + '][dbo].[' + @strGUID + ']') AND type in (N'P', N'PC'))
>BEGIN
> SET @operation = 'ALTER'
> --TODO: comment-updating feature (this will involve some parsing and text
>replacement)
> -- right now we won't mess with any existing content
> RETURN
>END
>ELSE
>BEGIN
> SET @operation = 'CREATE'
>END
>
>DECLARE @SQL varchar(8000)
>SET @SQL = '
>USE <@DATABASE>
>;
><@OPERATION@> PROCEDURE [dbo].[<@SPNAME@>]
>AS
>/*
><@COMMENTS@>
>*/
>PRINT ''NEW STORED PROCEDURE''
>'
>
>SET @SQL = REPLACE(@SQL, '<@DATABASE>', @Database)
>SET @SQL = REPLACE(@SQL, '<@OPERATION@>', @operation)
>SET @SQL = REPLACE(@SQL, '<@SPNAME@>', @strGUID)
>SET @SQL = REPLACE(@SQL, '<@COMMENTS@>', @Comments)
>
>PRINT 'Executing generated SQL:
>' + @SQL
>Execute(@SQL);
>

[Database Administrator]

Yep, I just figured that out

Paul

----

ALTER PROCEDURE [dbo].[DC1BF183-FD5F-4025-9549-B90A0E07AD29]
@Database varchar(100),
@GUID uniqueidentifier,
@Comments varchar(8000)
AS

DECLARE @strGUID varchar(50)
SET @strGUID = CAST(@GUID as varchar(50))

DECLARE @ChangeDBSQL varchar(2000)
SET @ChangeDBSQL = 'USE <@DATABASE@>;'
SET @ChangeDBSQL = REPLACE(@ChangeDBSQL, '<@DATABASE@>', @Database)
PRINT 'Executing generated SQL (to change database):
' + @ChangeDBSQL
Execute(@ChangeDBSQL);

SET @Comments = REPLACE(@Comments, '''', '')

DECLARE @operation varchar(30)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['
+ @strGUID + ']') AND type in (N'P', N'PC'))
BEGIN
SET @operation = 'ALTER'
--TODO: comment-updating feature (this will involve some parsing and text
replacement)
-- right now we won't mess with any existing content
RETURN
END
ELSE
BEGIN
SET @operation = 'CREATE'
END

DECLARE @SQL varchar(8000)
SET @SQL = '
<@OPERATION@> PROCEDURE [dbo].[<@SPNAME@>]
AS
/*
<@COMMENTS@>
*/
PRINT ''NEW STORED PROCEDURE''
'
SET @SQL = REPLACE(@SQL, '<@OPERATION@>', @operation)
SET @SQL = REPLACE(@SQL, '<@SPNAME@>', @strGUID)
SET @SQL = REPLACE(@SQL, '<@COMMENTS@>', @Comments)

PRINT 'Executing generated SQL:
' + @SQL
Execute(@SQL);

[Database Administrator]

Actually, oops, that doesn't work.

Paul

[Database Administrator]

On Wed, 6 Jun 2007 15:08:54 -0400, "PJ6" wrote:

>Actually, oops, that doesn't work.
>
>Paul

Ii seems to work in this simple example.

CREATE PROC Demo1
AS
declare @sql varchar(500)

SET @sql = 'CREATE PROC Demo2 AS SELECT COUNT(*) as Rows FROM
sysobjects'

EXEC (@sql)
GO

EXEC Demo1

EXEC Demo2

Rows
-----------
106

Roy Harvey
Beacon Falls, CT

[Database Administrator]

This example doesn't test for the existence of or create a new stored
procedure in a *different* database.

I believe that it is impossible to do either using a stored procedure given
the current 2000 and 2005 implementations of SQL Server.

Paul

"Roy Harvey" wrote in message
news:6v2e63tcr6gp5s66cdhj6ge29qdvltkbkr@4ax.com...
> On Wed, 6 Jun 2007 15:08:54 -0400, "PJ6" wrote:
> Ii seems to work in this simple example.
>
> CREATE PROC Demo1
> AS
> declare @sql varchar(500)
>
> SET @sql = 'CREATE PROC Demo2 AS SELECT COUNT(*) as Rows FROM
> sysobjects'
>
> EXEC (@sql)
> GO
>
> EXEC Demo1
>
> EXEC Demo2
>
> Rows
> -----------
> 106
>
> Roy Harvey
> Beacon Falls, CT

[Database Administrator]

On Jun 18, 1:29 pm, "PJ6" wrote:
> This example doesn't test for the existence of or create a new stored
> procedure in a *different* database.
>
> I believe that it is impossible to do either using a stored procedure given
> the current 2000 and 2005 implementations of SQL Server.

See this thread from 2005 on this subject:

http://tinyurl.com/2fgdee


The trick is to double-nest EXECs, with extra quotes. Here is a basic
example that I just tested on SQL2000. It creates a procedure in
Master that can execute arbitary code, including CREATE statements, in
another database context.

use master
go

drop procedure Executor
go

CREATE PROCEDURE Executor (@dbname varchar(32), @SQL varchar(8000)) AS
declare @z varchar(8000)
set @z='USE ' + @dbname + '
EXECUTE ('' '+ @SQL +' '')
'

exec(@z)

GO

EXEC master.dbo.Executor 'model','CREATE PROCEDURE fnord AS PRINT
DB_NAME()+convert(varchar,getdate())'

select ROUTINE_CATALOG,ROUTINE_NAME from
model.INFORMATION_SCHEMA.ROUTINES

[Database Administrator]

Wow. Thank you, that just made my day

Paul

"rpresser" wrote in message
news:1182196921.688554.49570@o61g2000hsh.googlegro ups.com...
> On Jun 18, 1:29 pm, "PJ6" wrote:
>> This example doesn't test for the existence of or create a new stored
>> procedure in a *different* database.
>>
>> I believe that it is impossible to do either using a stored procedure
>> given
>> the current 2000 and 2005 implementations of SQL Server.
>
> See this thread from 2005 on this subject:
>
> http://tinyurl.com/2fgdee
>
>
> The trick is to double-nest EXECs, with extra quotes. Here is a basic
> example that I just tested on SQL2000. It creates a procedure in
> Master that can execute arbitary code, including CREATE statements, in
> another database context.
>
> use master
> go
>
> drop procedure Executor
> go
>
> CREATE PROCEDURE Executor (@dbname varchar(32), @SQL varchar(8000)) AS
> declare @z varchar(8000)
> set @z='USE ' + @dbname + '
> EXECUTE ('' '+ @SQL +' '')
> '
>
> exec(@z)
>
> GO
>
> EXEC master.dbo.Executor 'model','CREATE PROCEDURE fnord AS PRINT
> DB_NAME()+convert(varchar,getdate())'
>
> select ROUTINE_CATALOG,ROUTINE_NAME from
> model.INFORMATION_SCHEMA.ROUTINES
>

[mkarthikeyancs]

hi,
just u add this line before then only it'll exec

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create proc ........
.........