如何提交或回滚此语句 [英] how do commit or rollback this statment

问题描述

我编写了可在多表上操作的代码..

如何提交或回滚所有语句?

i write this code that Operate On Multi Table ..

How Do I Commit Or RollBack All Statement ?

<pre>set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER PROCEDURE [dbo].[Sell_Main_Insert]
	(
	@sm_id						[bigint],
	@sm_cus_id						[bigint],
	@sm_date						[nvarchar](10),
	@sm_time						[nvarchar](10),
	@sm_rebate						[bigint] ,
	@sm_pardakht					[bigint] ,
	
	@smd_sm_id						[bigint] ,
	@smd_cus_id						[bigint] ,
	@smd_product_id1				[bigint] ,
	@smd_product_id2				[bigint] ,
	@smd_product_id3				[bigint] ,
	@smd_product_id4				[bigint] ,
	@smd_product_id5				[bigint] ,
	@smd_product_price1				[bigint] ,
	@smd_product_price2				[bigint] ,
	@smd_product_price3				[bigint] ,
	@smd_product_price4				[bigint] ,
	@smd_product_price5				[bigint] 
	)
AS 
--*********************************************************************************
if exists (select * from [Sell-Main] where [sm_id]=@sm_id)
return 3

if not exists (select * from [Customers] where [cus_id]=@sm_cus_id)
return 2

if @smd_product_id1 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id1)
return 4

if @smd_product_id2 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id2)
return 5

if @smd_product_id3 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id3)
return 6

if @smd_product_id4 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id4)
return 7

if @smd_product_id5 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id5)
return 8


--*********************************************************************************
INSERT INTO [Sell-Main]
	(
	sm_id,
	sm_cus_id,
	sm_date,
	sm_time,
	sm_rebate,
	sm_pardakht
	)
VALUES
	(
	@sm_id,
	@sm_cus_id,
	@sm_date,
	@sm_time,
	@sm_rebate,
	@sm_pardakht
	)
--*********************************************************************************
declare @count_checker int
select @count_checker=count(*) from [Sell-Main-Details]
if @count_checker<>0
begin 
declare @smd_id_number1 int
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
end
else
begin
--declare @smd_id_number1 int
set @smd_id_number1=1
end
--*********************************************************************************
if @smd_product_id1 <> 0
begin

INSERT INTO [Sell-Main-Details]
	(
	smd_id,
	smd_sm_id,
	smd_cus_id,
	smd_product_id,
	smd_product_price
	)
VALUES
	(
	@smd_id_number1+1,
	@smd_sm_id,
	@smd_cus_id,
	@smd_product_id1,
	@smd_product_price1
	)
end

--*********************************************************************************
declare @smd_id_number2 int
select @smd_id_number2=max(smd_id) from [Sell-Main-Details]
--*********************************************************************************
if @smd_product_id2 <> 0
begin

INSERT INTO [Sell-Main-Details]
	(
	smd_id,
	smd_sm_id,
	smd_cus_id,
	smd_product_id,
	smd_product_price
	)
VALUES
	(
	@smd_id_number2+1,
	@smd_sm_id,
	@smd_cus_id,
	@smd_product_id2,
	@smd_product_price2
	)
end

--*********************************************************************************
declare @smd_id_number3 int
select @smd_id_number3=max(smd_id) from [Sell-Main-Details]
--*********************************************************************************
if @smd_product_id3 <> 0
begin

INSERT INTO [Sell-Main-Details]
	(
	smd_id,
	smd_sm_id,
	smd_cus_id,
	smd_product_id,
	smd_product_price
	)
VALUES
	(
	@smd_id_number3+1,
	@smd_sm_id,
	@smd_cus_id,
	@smd_product_id3,
	@smd_product_price3
	)
end

--*********************************************************************************
declare @smd_id_number4 int
select @smd_id_number4=max(smd_id) from [Sell-Main-Details]
--*********************************************************************************
if @smd_product_id4 <> 0
begin

INSERT INTO [Sell-Main-Details]
	(
	smd_id,
	smd_sm_id,
	smd_cus_id,
	smd_product_id,
	smd_product_price
	)
VALUES
	(
	@smd_id_number4+1,
	@smd_sm_id,
	@smd_cus_id,
	@smd_product_id4,
	@smd_product_price4
	)
end

--*********************************************************************************
declare @smd_id_number5 int
select @smd_id_number5=max(smd_id) from [Sell-Main-Details]
--*********************************************************************************
if @smd_product_id5 <> 0
begin

INSERT INTO [Sell-Main-Details]
	(
	smd_id,
	smd_sm_id,
	smd_cus_id,
	smd_product_id,
	smd_product_price
	)
VALUES
	(
	@smd_id_number5+1,
	@smd_sm_id,
	@smd_cus_id,
	@smd_product_id5,
	@smd_product_price5
	)
end

--*********************************************************************************
declare @Customer_Score int
set @Customer_Score=(@smd_product_price1 + @smd_product_price2 + @smd_product_price3 + @smd_product_price4 + @smd_product_price5)/1000
update Customers set [cus_score]=[cus_score]+@Customer_Score where [cus_id]=@sm_cus_id
--*********************************************************************************

return 1

推荐答案

您必须使用BEGIN TRAN来开始交易.然后，在每条语句之后检查错误，如果有错误，则可以回滚事务.您还可以根据您的SQL版本使用try catch.

请参见此处. [

You have to use BEGIN TRAN to start a transaction. Then after each statement you check for an error and if there is one you can rollback the transaction. You can also use try catch depending on your version of SQL.

See here.[^]

这篇关于如何提交或回滚此语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！