T-SQL. Text of procedure call

Question

I'm trying to implement a generic logging for my stored procedures. The best solution I found is to use DBCC INPUTBUFFER, it returns the text of procedure call like:

DECLARE @a INT
SET @a = 1000
EXEC usp_Test @param = @a

But it has one limitation, the max length of this buffer is 4000. I have a lot of procedures that have table valued parameters and often they contain > 10000 records, so I can't log this call with this approach.

Is there any way to implement such logging without manual creating of 'Text of procedure call' in each procedure?

Answer 1

Instead of using DBCC INPUTBUFFER @SPID, you can try to use the dm_exec_sql_text

It has a nvarchar(max) field as Text of the last SP.

Try to build a function for this code (expect the @SPID as int parameter):

--Select the sql_handle first for the given session ID
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @SPID

--Select the last statement
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)

An other way to use:

EXEC yourProcedure withYourParams
SELECT @sqltext = sql_handle FROM sys.sysprocesses WHERE spid = @@SPID

SELECT TEXT FROM ::fn_get_sql(@sqltext)

Instead of @SPID parameter for this, you can use the @@SPID, but then this code segment will be integrated with your last SP call.