CODEWITHSUNDEEP
sqlsql-serverdebuggingstored-procedures
Tuukka Haapaniemi
Tuukka Haapaniemi

Reputation: 1274

Last executed statement in SQL Server stored procedure

Is there a way to print the last executed SQL statement wihin a stored procedure?

I found out about 

DBCC INPUTBUFFER(@@SPID)

but that only prints the last bigger portion, i.e. the procedure run. I'd like to accomplish the same for each statement run from within the stored procedure.

We have a huge procedure with about 30 SQL statements. We'd like this procedure to output all the SQL statements when run with @debug = 1 parameter. We've had success with doing that when the queries are dynamic and constructed into a varchar that is run with EXEC, but what about non-dynamic queries? Is it possible to print those as well?

Upvotes: 0

Views: 1757

Answers (1)

Bridge
Bridge

Reputation: 30711

Perhaps this may be of help, dug this one out of an old library of scripts. Should show the last queries run in order of newest first. You can add TOP n to the select if you just want a few of them.

SELECT ProcName = COALESCE(OBJECT_NAME(S2.objectid), 'Ad-Hoc'),
       s1.execution_count,
       sql_statement = (SELECT TOP 1 SUBSTRING(S2.TEXT, S1.statement_start_offset / 2 + 1, ((CASE
                                                                                               WHEN S1.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), S2.TEXT)) * 2)
                                                                                               ELSE S1.statement_end_offset
                                                                                             END) - S1.statement_start_offset) / 2 + 1)),
       S1.last_execution_time
FROM   sys.dm_exec_query_stats AS S1
       CROSS APPLY sys.Dm_exec_sql_text(s1.sql_handle) AS S2
ORDER  BY last_execution_time DESC

Upvotes: 2

Related Questions