when running stored procedure, which records got added, updated & deleted

Question

when running stored procedure, which records got added, updated & deleted

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
July 31, 2023 at 3:42 pm

#4249305

When Running a stored procedure how i can track which records got created, updated and deleted as a result of execution of Stored Procedure.
Is this Possible? I want to know the DML's.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

The stored procedure is the DML.

If you want to know which rows are updated, and the stored procedure isn't updating specified rows (e.g., ID parameter), then you'll probably need trigger(s) to store that info into a tracking table.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

You can also use the OUTPUT clause to return this information. If you want to know later, you have to store this information somewhere. This isn't tracked by SQL SErver by default

OUTPUT - https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 3

The best way to track this information is have it be part of the Stored Procedure you are running and have a table that houses this information in a logical manner.

Yes, in some situations this information can be critical, as we track this information for some of the stored procedures we run but, as stated, we built this feature into the stored procedure since that is the most efficient place to have it exists.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

You could look to Change Data Capture as a way to see all the data manipulation on the system. It does require quite a bit of setup & maintenance though, so read through the docs carefully.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

simiritipandey Grasshopper Points: 11 More actions · Answer 5

To track changes from a stored procedure:

Output Parameters: Use output parameters to capture the number of affected rows for inserts, updates, and deletes.
Audit Tables: Store changes in separate audit tables within the procedure.
Change Data Capture (CDC): If supported, use CDC to automatically track changes.
Triggers: Implement triggers to log changes to a separate table.
Transaction Logs: Query transaction logs for detailed change info.
Versioning/Timestamp Columns: Check versioning/timestamp columns after the procedure runs.Choose the method that suits your needs and database capabilities. Consider performance implications.

This reply was modified 1 year, 3 months ago by Steve Jones - SSC Editor.

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 6

I would have a log table to keep track of the updates, etc, and maintain this table regularly. It is quite simple.

DBASupport