本文介绍了如何提交或回滚此语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

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

推荐答案


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

08-26 07:10
查看更多