CODEWITHSUNDEEP
sql-serverstored-procedures
Victor de los santos
Victor de los santos

Reputation: 1

Pass parameter to nested procedure or create temp table and check if table exists

I have the following structure of stored procedures:

exec A -> exec B -> exec C -> exec D

where stored procedure A calls stored procedure B, B calls C and C calls D.

I need a special behavior to happen in stored procedure D based on a condition that is determined in stored procedure A. Of course one option is to pass param from A to B, then from B to C, then from C to D, but I was wondering if I can simply just create a temp table prior to executing A, then when I am in stored procedure D I can just check if temp table exists and perform my logic based on that.Finally in the line right after exec A, I will drop the temp table

EDIT: I am aware that if proc D is called from a different procedure the condition will be treated as not exists. Also I am aware of temp table name can clash with another temp table with the same name. Other than that...

Is there any unwanted side effects to doing the later solution?

Upvotes: 0

Views: 462

Answers (2)

ANDREI FOMITCHEV
ANDREI FOMITCHEV

Reputation: 94

I usually use a persistent [Params] table. I have procedure:

CREATE OR ALTER PROCEDURE SetParam @name VarChar(20), @value VarChar(50) AS BEGIN
    -- I don't like MERGE, but it could be used here as well.
    BEGIN TRY
        INSERT INTO [Params] ([Name], [Value]) 
        VALUES (@name, @value)
    END TRY
    BEGIN CATCH
        UPDATE [Params] SET [Value] = @Value
        WHERE [Name] = @name
    END CATCH
END
GO
CREATE OR ALTER PROCEDURE GetParam @name VarChar(20), @value VarChar(50) OutPut AS BEGIN
    SET @value = NULL
    -- Try - Catch could be here as well
    SELECT @value FROM [Params] WHERE [Name]=@name
END
GO

-- In A
EXEC SetParam 'Param','Value'

-- In D
DECLARE @ParamValue Int
EXEC GetParam 'ParamName', @ParamValue OutPut

Upvotes: 0

Julio Alfredo
Julio Alfredo

Reputation: 11

The temp tables or (#tables) should have the lifespan of the session. In the case you describe you should be able to create the #table in PROC A and reference it on PROC D without any special considerations.

Please note that if you take this approach you are creating a direct dependency between PROC D and PROC A, since now you have to call PROC A to create the Temp table you are using on PROC D. If you are OK with this then go ahead.

Upvotes: 1

Related Questions