CODEWITHSUNDEEP
sql-servert-sqlloggingstored-procedures
Eugene
Eugene

Reputation: 1535

T-SQL. Text of procedure call

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?

Upvotes: 9

Views: 1138

Answers (2)

user1896587
user1896587

Reputation: 9

what about change data capture?

http://msdn.microsoft.com/en-us/library/bb510744(v=sql.105).aspx

or an OUTPUT statement

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Upvotes: 0

András Ottó
András Ottó

Reputation: 7695

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.

Upvotes: 4

Related Questions