how I can know the SP that call my SP

Question

how I can know the SP that call my SP

lleston

SSC-Addicted

Points: 487
More actions
April 7, 2005 at 10:57 am

#66180

Hi guys,
There are same way to know what Stored Procedure calling my actual SP.
When I use @@NestLevel returns 2, but I wanna know witch SP is in the first level. Is it possible?
e.g.
CREATE PROC usp_test_1
as
EXEC usp_test_2
go
CREATE PROC usp_test_2
as
IF @@NestLevel > 1
print '<< SP NAME >>'
go

Viewing 3 posts - 1 through 2 (of 2 total)

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

vadba SSChampion Points: 11133 More actions · Answer 1

Maybe this example using DBCC INPUTBUFFER will help. The only problem is, I've never figured out how to supress the "DBCC Execution completed." message.

/*

DROP PROCEDURE P1a

DROP PROCEDURE P1b

DROP PROCEDURE P2

*/

GO

CREATE PROCEDURE P2

AS

DECLARE @inputBuffer varchar(255)

CREATE TABLE #InputBuffer

(

EventType varchar(15)

, Parameters int

, EventInfo varchar(255)

)

SET ANSI_WARNINGS OFF

SET NOCOUNT ON

INSERT #InputBuffer EXEC ('dbcc inputbuffer( @@spid )')

SELECT TOP 1 @inputBuffer = EventInfo

FROM #InputBuffer

DROP TABLE #InputBuffer

PRINT 'P2 called by ' + @inputBuffer

GO

CREATE PROCEDURE P1a

AS

PRINT 'P1a calling P2'

EXEC P2

GO

CREATE PROCEDURE P1b

AS

PRINT 'P1b calling P2'

EXEC P2

GO

P1a

GO

P1b

GO

P2

GO

EXEC P1a

EXEC P1b

EXEC P2

GO

lleston SSC-Addicted Points: 487 More actions · Answer 2

Thanks "mkeast",

This example works ok.

To supress the message from DBCC use:

DBCC <<any>> WITH NO_INFOMSGS

Regards.