Stored Procedures Inside Stored Procedures

  • chadswt

    SSC Veteran

    Points: 200

    Comments posted to this topic are about the item Stored Procedures Inside Stored Procedures

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1226

    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'')';
  • morganw

    SSC Rookie

    Points: 26

    Very creative!

  • breadcrumb

    SSC Veteran

    Points: 205

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply