Last Statement Executed?
-
Does anyone know in 2K5 if there is a command that returns the last statement executed within a stored proc.
Basically what I'm looking for is I have a proc that gets passed in a bunch of parameters for updating a single row in a table... once the statement finishes I want to put the statement that just got executed into a tracking/logging table and I know I could build the statement myself with some dynamic SQL but I was hoping for a bit more of an elegant solution that wouldn't require constant maintenance if parameters change, etc
Does that make sense?
And please keep in mind when you reply that this is all within the same stored proc and I don't know the spid that will be running the proc, etc so a DBCC INPUTBUFFER(spid) wont work for me.
Thanks
-
Also I DO NOT want to use an update trigger againt the table
-
Colin,
how about using fn_get_sql? That should return the last statement that was run (even if it's in a procedure).
DECLARE @handle binary(20)
SELECT @handle = sql_handle
FROM master..sysprocesses
WHERE spid = @@SPID
SELECT [text]
FROM ::fn_get_sql(@handle)
-
Perhaps I misunderstood your solution and how you wanted me to impliment this but this is the results I got when I used the fn_get_sql
So what I have inside my proc is
Alter proc sp_blah @c1, @c2, @c3 (about 10 different params)
As
Update tbl
Set col1 = @c1
...
...
...
and then I put in the code from your reply
got the statement
Inserted it into a tracking table
and what I got as a result in the tracking table is the full listing of the stored proc.... right from the Create Stored Procedure all the way through till the end statement
What I am looking to trap is the actual Update Statement that got ran... not the whole stored proc definition... if all else fails I could through dymanic SQL determine the Update statement but that is a really ugly solution and doesn't allow for easy changes without a rewrite of the code to pull the dynamic SQL
Thanks for the reply and I appreciate the post but I need the actual Update Statement with the parameters filled in, etc.
-
You know, I could have sworn that fn_get_sql returned the very last statement that executed but it looks like I was wrong.
I must have imagined that. Sorry.
Yeah, the dynamic sql solution is ugly.
Could you get away with storing just the parameters in your tracking table, along with the table being updated? You don't get to store the entire update statement but in storing the table name and parameters that might be enough to give you a clue as to what the update statement did.
Something like this:
update tbl
set col1 = @c1,
...
...
--now update the tracking table
update tracking_table
set table_name = 'tbl',
param1 = @c1,
...
...
-
hmmmm that seems a bit ugly and I might have to go with a dymanic pulling of the statement.
using the tracking table with the individual columns, etc seems a bit TOO hack-fu for me and would require alot more maintenance and holding of information
Thanks for the try though
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply