See values of a proc when it is called from another proc?

Transact-SQL
,
1

I have SQL Server 2012 Enterprise. I have run SQL Profiler to track calls of my stored procedures. I have specified SP:Starting event for this. There was a situation when my client application calls a procedure Proc1; and Proc1 calls procedure Proc2. Profiler displays the both calls – OK. But I see that Profiler displays actual values of procedure parameters (“@param1 = xxx, @param2= yyy” etc) only for Proc1. For Proc2 it does not show the actual values; it just enlists the parameters (“@param1, @param2” etc).

Is there a way to make SQL Profiler display the actual values for Proc2? Or maybe some other (free) profiler can do this?

2

I would use extended events for this:

Extended Events and Stored Procedure Parameter Values - Grant Fritchey (scarydba.com)

You should be aware that SQL Server 2012 has reached end of support. I would refuse to do anything except ugrading it :wink:

3

I seem to remember there was no way to get the called SP's parameter values with either extended events or profiler. For debugging purposes you may just have to manually log the parameter values within one of the SPs.

4

I see what you mean and you are right :slight_smile:

On SQL 2022 it shows the parameters of the called stored procedure:

CREATE OR ALTER PROCEDURE [dbo].[sp1]
	@Message VARCHAR(255) = 'Tell me the secret'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        SET @Message='You are right'
	EXECUTE dbo.sp2 @Message=@Message
    
END

and:

CREATE OR ALTER   PROCEDURE [dbo].[sp2]
	@Message VARCHAR(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT GetDate();
    
END

It wil show this info:

statement EXECUTE dbo.sp2 @Message=@Message

statement EXEC [dbo].[sp1]

5

"On SQL 2022 it shows the parameters of the called stored procedure" - I see...but as I said, I (more exactly - my customer) work with SQL server 2012 :frowning:

6

Thanks. Good to know.

7

So it shows only the parameters, not the values of the parameters