Is it possible to get if a stored procedure is called within another stored procedure?

Question

I have 2 stored procedures where the first one calls the second one within a transaction. The second procedure should never be called directly, but only from within its parent.

Currently, to check if this is the case I'm doing the following in the second procedure:

DECLARE @inTran bit;
IF @@TRANCOUNT > 0
    SET @inTran= 0
ELSE
    SET @inTran= 1

Is this correct? Is there a better way to do this?

Answer 1

If you are just looking for a casual way to prevent inadvertent execution of the proc on its own. You could also check @@NESTLEVEL - this will be at least 2 if called from another proc.

CREATE PROCEDURE Child
AS
    IF @@NESTLEVEL < 2 OR @@TRANCOUNT = 0
    THROW 50000, 'Child proc should be called from Parent', 1;

Or you could have the parent proc set a value read by SESSION_CONTEXT() in the child proc.

None of these will prevent the proc not being run as intended by someone determined to circumvent the restrictions though. They will just guard against accidental misuse.