Click here to Skip to main content
14,166,833 members
Rate this:
 
Please Sign up or sign in to vote.
I have multiple sql server stored procedures which have multiple insert, update & delete statements in each of them.

This app is a Legal Case management software

Once such procedure inserts records in two table and updates 4 other tables and delete record from one table.

How do i ensure that all sql query completed successfully without any errors or rollbacks?

Any method in sql server to check data integrity as it is called.

Please advise

What I have tried:

I have tried using @@ROWCOUNT to check if all records got inserted in the specific table
Posted
Updated 12-Mar-19 1:36am
Comments
F-ES Sitecore 12-Mar-19 7:12am
   
Surely if it doesn't throw an exception then it has succeeded with errors? If you want to make sure it has actually "done work" then @@ROWCOUNT is the traditional way of doing that.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

If it were me, I'd setup a logging mechanism that each of the stored procs update. At that point, determining success/failure would be as simple as querying the log table.
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Without knowing exactly your need, i would start by returning a synapses of the events that transpired; kinda like what you thought with the @@RowCount

One option would be to add in an a variable (eg @TotalRows) and increment that after each statement
DECLARE @TotalRows INT = 0

-- Existing INSERT statement 1
SET @TotalRows = @TotalRows + @@RowCount

-- Existing INSERT statement 2
SET @TotalRows = @TotalRows + @@RowCount

-- Existing UPDATE statement 1
SET @TotalRows = @TotalRows + @@RowCount

-- Existing UPDATE statement 2
SET @TotalRows = @TotalRows + @@RowCount

-- Existing DELETE statement 1
SET @TotalRows = @TotalRows + @@RowCount

-- Existing DELETE statement 2
SET @TotalRows = @TotalRows + @@RowCount

-- End of your existing code
SELECT TotalRows = @TotalRows

If you needed some more detail on the results instead of just the running sum, you could use a variable table to give a line-by-line resultset, and populate each step of the way
DECLARE @Results TABLE (ndx INT, TableName NVARCHAR(16), CRUD NCHAR(1), Rows INT)

-- Existing INSERT statement 1
INSERT @Results VALUES (1, 'Table1', 'C', @@RowCount)

-- Existing INSERT statement 2
INSERT @Results VALUES (2, 'Table2', 'C', @@RowCount)

-- Existing UPDATE statement 1
INSERT @Results VALUES (3, 'Table3', 'U', @@RowCount)

-- Existing UPDATE statement 2
INSERT @Results VALUES (4, 'Table4', 'U', @@RowCount)

-- Existing DELETE statement 1
INSERT @Results VALUES (5, 'Table5', 'D', @@RowCount)

-- Existing DELETE statement 2
INSERT @Results VALUES (6, 'Table6', 'D', @@RowCount)

-- End of your existing code
SELECT * FROM @Results ORDER BY ndx

You could also go more advanced with the @Results version by using the OUTPUT clause to insert specific keys or other identifying information into the table
OUTPUT Clause (Transact-SQL) - SQL Server | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190518.1 | Last Updated 12 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100