如何确保正确完成多个SQL查询 [英] How to ensure multiple SQL query completed properly

问题描述

我有多个sql server存储过程，它们有多个insert，update&删除每个语句。



这个应用程序是一个法律案例管理软件



一旦这样的程序在两个表中插入记录并更新4个其他表并从一个表中删除记录。



如何确保所有sql查询成功完成而没有任何错误或回滚？



sql server中的任何方法都可以检查数据的完整性。



请指教


我尝试了什么：



我尝试过使用@@ ROWCOUNT来检查如果所有记录都插入到特定的表中

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

推荐答案

如果是我，我会设置一个记录机制，每个存储过程都会更新。此时，确定成功/失败就像查询日志表一样简单。

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.

在不知道您的需求的情况下，我将首先返回发生的事件的突触;有点像你想到的那样 @@ RowCount



一个选项就是添加一个变量（例如 @TotalRows ）并在每个语句

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

您还可以使用OUTPUT子句将特定键或其他识别信息插入到@Results版本中。表格

OUTPUT子句（Transact-SQL） - SQL Server | Microsoft Docs [ ^ ]

这篇关于如何确保正确完成多个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！