creating a stored procedure using a stored procedure

creating a stored procedure using a stored procedure

Post by PJ6 » Thu, 07 Jun 2007 03:24:10


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);
 
 
 

creating a stored procedure using a stored procedure

Post by Roy Harve » Thu, 07 Jun 2007 04:25:53

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

 
 
 

creating a stored procedure using a stored procedure

Post by PJ6 » Thu, 07 Jun 2007 04:40:21

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);
 
 
 

creating a stored procedure using a stored procedure

Post by PJ6 » Fri, 08 Jun 2007 04:08:54

Actually, oops, that doesn't work. :(

Paul
 
 
 

creating a stored procedure using a stored procedure

Post by Roy Harve » Fri, 08 Jun 2007 04:33:45


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
 
 
 

creating a stored procedure using a stored procedure

Post by PJ6 » Wed, 20 Jun 2007 02:29:23

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
 
 
 

creating a stored procedure using a stored procedure

Post by rpresse » Wed, 20 Jun 2007 05:02:01


See this thread from 2005 on this subject:

http://www.yqcomputer.com/


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
 
 
 

creating a stored procedure using a stored procedure

Post by PJ6 » Sun, 05 Aug 2007 01:58:36

Wow. Thank you, that just made my day :)

Paul