How to ensure multiple SQL query completed properly

Question

0.00/5 (No votes)

See more:

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 11-Mar-19 21:01pm

Christopher Fernandes

Updated 12-Mar-19 1:36am

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

#realJSOP · Answer 1 · 2019-03-12T00:55:00

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.

Posted 12-Mar-19 0:55am

#realJSOP

MadMyche · Answer 2 · 2019-03-12T01:36:00

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

SQL

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

SQL

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[^]