December 10, 2019 at 3:14 am
Comments posted to this topic are about the item Stored Procedures Inside Stored Procedures
December 13, 2019 at 1:13 pm
You don't need the replace, better to use double quotes, like:
SET @SuccessQry =
'CREATE PROCEDURE #Success
@ProcessStep VARCHAR(50)
AS
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES
(@ProcessStep, ''Success'')';
SET @FailureQry =
'CREATE PROCEDURE #Failure
@ProcessStep VARCHAR(50)
AS
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES
(@ProcessStep, ''Failure'')';
December 20, 2019 at 3:16 pm
Very creative!
January 2, 2020 at 2:45 pm
I'm a little confused as to why this approach is better than, say, just creating the stored procedures as non-temporary stored procedures? (yes, even non-temporary stored procedures can access temp tables that are defined in an prior scoped stored proc. The trick is, you have to create the Temp table before you define the stored procedures:
CREATE TABLE #DataLog
(ProcessStep VARCHAR(50),
StartDate DATETIME DEFAULT GETDATE(),
StatusDesc VARCHAR(20));
GO
CREATE PROCEDURE dbo.Success
@ProcessStep VARCHAR(50)
AS
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES
(@ProcessStep, 'SUCCESS');
GO
CREATE PROCEDURE dbo.Failure
@ProcessStep VARCHAR(50)
AS
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES
(@ProcessStep, 'FAILURE');
GO
CREATE PROCEDURE dbo.RunProcInProc
AS
SET NOCOUNT ON;
DROP TABLE IF EXISTS #DataLog;
CREATE TABLE #DataLog
(ProcessStep VARCHAR(50),
StartDate DATETIME DEFAULT GETDATE(),
StatusDesc VARCHAR(20));
EXEC dbo.Success @ProcessStep = 'Do Something Right';
EXEC dbo.Failure @ProcessStep = 'Do Something Wrong';
EXEC dbo.Success @ProcessStep = 'Back On Track';
SELECT *
FROM #DataLog;
GO
DROP PROCEDURE IF EXISTS dbo.RunProcInProc;
DROP PROCEDURE IF EXISTS dbo.Success;
DROP PROCEDURE IF EXISTS dbo.Failure;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply