Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

creating a stored procedure using a stored procedure


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

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

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

-----------------------------------------------Reply-----------------------------------------------

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

Paul

-----------------------------------------------Reply-----------------------------------------------

On Wed, 6 Jun 2007 15:08:54 -0400, "PJ6" <n@nowhere.net> 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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc