January 11, 2005 at 12:39 am
Hi gurus,
Is there any way of creating a SP from a SP
My Blog:
January 11, 2005 at 2:49 am
This worked for me; I've commented out the DROP part so you can see the SP has been created:
USE Northwind
GO
CREATE PROCEDURE sp_SPinSP_1
AS
DECLARE @sSQL varchar(255)
SELECT @sSQL = 'CREATE PROCEDURE sp_SPinSP_2 AS SELECT * FROM Customers'
EXEC(@sSQL)
EXEC sp_SPinSP_2
--SELECT @sSQL = 'DROP PROCEDURE sp_SPinSP_2'
--EXEC(@sSQL)
GO
January 12, 2005 at 9:05 pm
well, I want an SP to be created in different DB, Sorry I could have mention this earlier.
My Blog:
January 13, 2005 at 3:01 am
This works but I don't know if it's the best way:
USE Northwind
GO
IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'sp_SPinSP_1' AND [type] = 'P')
DROP PROCEDURE dbo.sp_SPinSP_1
GO
CREATE PROCEDURE dbo.sp_SPinSP_1
AS
DECLARE @oSQLServer int
DECLARE @oDatabase int
DECLARE @CurrentDB varchar(255)
DECLARE @sql varchar(400)
DECLARE @Error int
DECLARE @ErrorMsg varchar(255)
DECLARE @Source varchar(255)
DECLARE @oQR int
EXEC sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
EXEC sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME, '<username>', '<password>'
SELECT @CurrentDB = 'Databases("pubs")'
EXEC sp_OAGetProperty @oSQLServer, @CurrentDB, @oDatabase OUT
SET @sql = 'CREATE PROCEDURE sp_SPinSP_2 AS SELECT * FROM employee'
EXEC @Error = sp_OAMethod @oDatabase, 'ExecuteImmediate', NULL, @sql
IF @Error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
PRINT @ErrorMsg
END
EXEC sp_OADestroy @oSQLServer
EXEC sp_OADestroy @oDatabase
GO
EXEC sp_SPinSP_1
GO
USE pubs
GO
EXEC sp_SPinSP_2
GO
--DROP PROCEDURE sp_SPinSP_2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply