如何在SQL中使用回滚创建存储过程(简单) [英] how to create stored procedure(Simple) in sql with rollback

问题描述

嗨！

我要检查存储过程中的错误.如何创建错误?请举一个非常简单的示例.

Hi !

I want check error in Stored Procedure.How it can be created ? Please give very simple example.

推荐答案

创建过程[dbo].[spGetPrint]

(
@PrintDate DateTime，
@StartUpFlag位
)
作为

开始交易
更新PrintList设置IsPrinted = 0其中1 = 0
选择
PrintID，PrintTypeID，PrintDate，MakerID，MakerTime，TerminalID，
AccMasterID，AccDetailsID，TransID，IsDuplicate
从PrintList，其中IsPrinted = 0并且(IsSelected = 0或@StartUpFlag = 1)
更新PrintList设置IsSelected = 1，其中IsPrinted = 0和IsSelected = 0
提交事务

create Procedure [dbo].[spGetPrint]

(
@PrintDate DateTime,
@StartUpFlag Bit
)
As

Begin Transaction
Update PrintList Set IsPrinted = 0 Where 1 = 0
Select
PrintID, PrintTypeID, PrintDate, MakerID, MakerTime, TerminalID,
AccMasterID, AccDetailsID, TransID, IsDuplicate
From PrintList Where IsPrinted = 0 And (IsSelected = 0 Or @StartUpFlag = 1)
Update PrintList Set IsSelected = 1 Where IsPrinted = 0 And IsSelected = 0
Commit Transaction

尝试一下，

开始尝试
开始交易
在这里查询SQL"
提交交易
结束尝试
开始捕捉
如果@@ TRANCOUNT> 0
回滚事务
结束捕获

Try this one,

Begin Try
Begin Transaction
"Wrie ur sql query here"
Commit Transaction
End Try
Begin Catch
If @@TRANCOUNT > 0
Rollback Transaction
End Catch

USE TempDB;
GO

CREATE TABLE ValueTable ([value] int)
GO

DECLARE @TransactionName varchar(20) = 'Transaction1';

--These statements start a named transaction,
--insert a two records, and then roll back
--the transaction named in the variable
--@TransactionName.
BEGIN TRAN @TransactionName
       INSERT INTO ValueTable VALUES(1)
       INSERT INTO ValueTable VALUES(2)
ROLLBACK TRAN @TransactionName

INSERT INTO ValueTable VALUES(3)
INSERT INTO ValueTable VALUES(4)

SELECT * FROM ValueTable

DROP TABLE ValueTable

这篇关于如何在SQL中使用回滚创建存储过程(简单)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！