如何提交或回滚此声明 [英] how do commit or rollback this statments
本文介绍了如何提交或回滚此声明的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我如何提交或回滚此语句?
请帮我吧
(对不起，我的大文字)
set ANSI_NULLS ON
设置 QUOTED_IDENTIFIER >打开
开始
更改 过程 [dbo].[Sell_Main_Update_Ghest]
(
@ sm_id_main [ bigint ]，
@ sm_cus_id_main [ bigint ]，
@ smd_product_id1_main [ bigint ]，
@ smd_product_id2_main [ bigint ]，
@ smd_product_id3_main [ bigint ]，
@ smd_product_id4_main [ bigint ]，
@ smd_product_id5_main [ bigint ]，
@ smd_id_main1 [ bigint ]，
@ smd_id_main2 [ bigint ]，
@ smd_id_main3 [ bigint ]，
@ smd_id_main4 [ bigint ]，
@ smd_id_main5 [ bigint ]，
@ sm_id [ bigint ]，
@ sm_cus_id [ bigint ]，
@ sm_date [ nvarchar ]( 10 ) ，
@ sm_time [ nvarchar ]( 10 ) ，
@ sm_rebate [ bigint ]，
@ sm_pardakht [ bigint ]，
@ smd_id [ 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 ]，
@ main_pay_sm_id [ bigint ]，
@ pay_ghest_count [ bigint ]，
@ pay_ghest_day [ bigint ]，
@ pay_sm_id [ bigint ]，
@ pay_cus_id [ bigint ]，
@ pay_ghest_price [ bigint ]
)
AS
- ************************ ****************************************************** *******
- 如果存在(从[Sell-Main]中选择*，其中[sm_id] = @ sm_id)
- 返回3
如果 不 存在(选择 * 来自 [客户] 其中 [cus_id] = @ sm_cus_id)
返回 2
如果 存在(选择 * 来自 [Sell-Main] 其中 [sm_id] = @ sm_id and [sm_id]<> @sm_id_main)
返回 3
如果 @ smd_product_id1 <> 0
如果 不 存在(选择 * 来自产品其中 [product_id] = @ smd_product_id1)
返回 4
如果 @ smd_product_id2 <> 0
如果 不 存在(选择 * 来自产品其中 [product_id] = @ smd_product_id2)
返回 5
如果 @ smd_product_id3 <> 0
如果 不 存在(选择 * 来自产品其中 [product_id] = @ smd_product_id3)
返回 6
如果 @ smd_product_id4 <> 0
如果 不 存在(选择 * 来自产品其中 [product_id] = @ smd_product_id4)
返回 7
如果 @ smd_product_id5 <> 0
如果 不 存在(选择 * 来自产品其中 [product_id] = @ smd_product_id5)
返回 8
如果 存在(选择 * 来自付款哪里付款.[pay_cus_id] = @ sm_cus_id_main >并 [pay_state] = ' پرداختشده')
返回 9
- ************************ ****************************************************** *******
更新 [Sell-Main]
设置
[sm_id] = @ sm_id，
[sm_cus_id] = @ sm_cus_id，
[sm_date] = @ sm_date，
[sm_time] = @ sm_time，
[sm_rebate] = @ sm_rebate，
[sm_pardakht] = @ sm_pardakht
其中
[sm_id] = @ sm_id_main
- ************************ ****************************************************** *******
更新 [出售详细信息]
设置
[smd_sm_id] = @ smd_sm_id，
[smd_cus_id] = @ smd_cus_id
其中
[smd_sm_id] = @ sm_id_main
- OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY
- OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY
- ************************ ****************************************************** *******
如果 @ smd_product_id1 <> @ smd_product_id1_main
开始
更新 [出售详细信息]
设置
[smd_sm_id] = @ smd_sm_id，
[smd_cus_id] = @ smd_cus_id，
[smd_product_id] = @ smd_product_id1，
[smd_product_price] = @ smd_product_price1
其中
[smd_id] = @ smd_id_main1
结束
- 其他
其他 如果 @ smd_product_id1 = 0 and @ smd_product_id1_main<> 0
删除 从 [Sell-Main-Details] 在何处 [smd_sm_id] = @ smd_id_main1
- 插入条件
如果 @ smd_product_id1<> 0 and @ smd_product_id1_main = 0
开始
声明 @ smd_id_number1 int
选择 @ smd_id_number1 = max(smd_id)来自 [销售主体详细信息]
插入 INTO [出售详细信息]
(
smd_id，
smd_sm_id，
smd_cus_id，
smd_product_id，
smd_product_price
)
值
(
@ smd_id_number1 + 1，
@ smd_sm_id ，
@ smd_cus_id ，
@ smd_product_id1 ，
@ smd_product_price1
)
结束
- ************************ ****************************************************** *******
如果 @ smd_product_id2 <> @ smd_product_id2_main
开始
更新 [出售详细信息]
设置
[smd_sm_id] = @ smd_sm_id，
[smd_cus_id] = @ smd_cus_id，
[smd_product_id] = @ smd_product_id2，
[smd_product_price] = @ smd_product_price2
其中
[smd_id] = @ smd_id_main2
结束
- 其他
否则 如果 @ smd_product_id2 = 0 and @ smd_product_id2_main<> 0
删除 从 [Sell-Main-Details] 在何处 [smd_sm_id] = @ smd_id_main2
- 插入条件
如果 @ smd_product_id2<> 0 and @ smd_product_id2_main = 0
开始
选择 @ smd_id_number1 = max(smd_id)来自 [销售主体详细信息]
插入 INTO [出售详细信息]
(
smd_id，
smd_sm_id，
smd_cus_id，
smd_product_id，
smd_product_price
)
值
(
@ smd_id_number1 + 1，
@ smd_sm_id ，
@ smd_cus_id ，
@ smd_product_id1 ，
@ smd_product_price1
)
结束
- ************************ ****************************************************** *******
如果 @ smd_product_id3 <> @ smd_product_id3_main
开始
更新 [出售详细信息]
设置
[smd_sm_id] = @ smd_sm_id，
[smd_cus_id] = @ smd_cus_id，
[smd_product_id] = @ smd_product_id3，
[smd_product_price] = @ smd_product_price3
其中
[smd_id] = @ smd_id_main3
结束
- 其他
其他 如果 @ smd_product_id3 = 0 and @ smd_product_id3_main<> 0
删除 从 [Sell-Main-Details] 在何处 [smd_sm_id] = @ smd_id_main3
- 插入条件
如果 @ smd_product_id3<> 0 and @ smd_product_id3_main = 0
开始
选择 @ smd_id_number1 = max(smd_id)来自 [销售主体详细信息]
插入 INTO [出售详细信息]
(
smd_id，
smd_sm_id，
smd_cus_id，
smd_product_id，
smd_product_price
)
值
(
@ smd_id_number1 + 1，
@ smd_sm_id ，
@ smd_cus_id ，
@ smd_product_id1 ，
@ smd_product_price1
)
结束
- ************************ ****************************************************** *******
如果 @ smd_product_id4 <> @ smd_product_id4_main
开始
更新 [出售详细信息]
设置
[smd_sm_id] = @ smd_sm_id，
[smd_cus_id] = @ smd_cus_id，
[smd_product_id] = @ smd_product_id4，
[smd_product_price] = @ smd_product_price4
其中
[smd_id] = @ smd_id_main4
结束
- 其他
其他 如果 @ smd_product_id4 = 0 and @ smd_product_id4_main<> 0
删除 从 [Sell-Main-Details] 在何处 [smd_sm_id] = @ smd_id_main4
- 插入条件
如果 @ smd_product_id4<> 0 and @ smd_product_id4_main = 0
开始
选择 @ smd_id_number1 = max(smd_id)来自 [销售主体详细信息]
插入 INTO [出售详细信息]
(
smd_id，
smd_sm_id，
smd_cus_id，
smd_product_id，
smd_product_price
)
值
(
@ smd_id_number1 + 1，
@ smd_sm_id ，
@ smd_cus_id ，
@ smd_product_id1 ，
@ smd_product_price1
)
结束
- ************************ ****************************************************** *******
如果 @ smd_product_id5 <> @ smd_product_id5_main
开始
更新 [出售详细信息]
设置
[smd_sm_id] = @ smd_sm_id，
[smd_cus_id] = @ smd_cus_id，
[smd_product_id] = @ smd_product_id5，
[smd_product_price] = @ smd_product_price5
其中
[smd_id] = @ smd_id_main5
结束
- 其他
其他 如果 @ smd_product_id5 = 0 and @ smd_product_id5_main<> 0
删除 从 [Sell-Main-Details] 在何处 [smd_sm_id] = @ smd_id_main5
- 插入条件
如果 @ smd_product_id5<> 0 and @ smd_product_id5_main = 0
开始
选择 @ smd_id_number1 = max(smd_id)来自 [销售主体详细信息]
插入 INTO [出售详细信息]
(
smd_id，
smd_sm_id，
smd_cus_id，
smd_product_id，
smd_product_price
)
值
(
@ smd_id_number1 + 1，
@ smd_sm_id ，
@ smd_cus_id ，
@ smd_product_id1 ，
@ smd_product_price1
)
结束
- ************************ ****************************************************** *******
- 声明@Customer_Score int
- 设置@Customer_Score =(@ smd_product_price1 + @ smd_product_price2 + @ smd_product_price3 + @ smd_product_price4 + @ smd_product_price5)/1000
- 更新客户设置为[cus_score] = [cus_score] + @ Customer_Score，其中[cus_id] = @ sm_cus_id
- ************************ ****************************************************** *******
删除 从 [删除] 其中 [pay_sm_id ] = @ main_pay_sm_id
- ++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++++
声明 @ count_checker3 int
选择 @ count_checker3 = count(*)来自
如果 @ count_checker3<> 0
开始
声明 @ pay_id_number > int
选择 @ pay_id_number = max(pay_id)来自 [支出]
结束
其他
开始
设置 @ pay_id_number = 1
结束
声明 @ datetime datetime
选择 @ datetime = getdate()
- DT =日期时间
声明 @ DT nvarchar ( 10 )
声明 @ i int
设置 @ i = 1
同时(@ i< = @ pay_ghest_count)
开始
选择 @ datetime = dateadd(dd， @ pay_ghest_day ， @日期时间)
选择 @ dt = convert( nvarchar ， @ datetime ， 111 )
插入 插入 [过程]
(
pay_id，
pay_sm_id，
pay_cus_id，
pay_price，
pay_date
)
值
(
@ pay_id_number + 1，
@ pay_sm_id ，
@ pay_cus_id ，
@ pay_ghest_price ，
@ DT
)
设置 @ i = @ i + 1
选择 @ pay_id_number = max(pay_id)来自 [支出]
结束
- ************************ ********************************************
- ************************ ********************************************
声明 @ pp1 int
设置 @ pp1 = 0
选择 @ pp1 = [sm_pardakht] 来自 [Sell-Main] 哪里 [sm_id] = @ sm_id_main
- ************************ ***************
声明 @ Customer_Score_old int
声明 @ Customer_Score_new int
设置 @ Customer_Score_old = 0
设置 @ Customer_Score_new = 0
设置 @ Customer_Score_old = @ pp1/1000
设置 @ Customer_Score_new = @ sm_pardakht/1000
- ************************ ***************
-
如果 @ sm_cus_id_main <> @ sm_cus_id
开始
更新客户 set [cus_score] = [cus_score]-@ Customer_Score_old，[cus_sell_count] = [cus_sell_count]- 1，[cus_ref_count] = [cus_ref_count] -1 其中 [cus_id] = @ sm_cus_id_main
更新客户设置 [cus_score] = [cus_score] + @ Customer_Score_new，[cus_sell_count] = [cus_sell_count] + 1，[cus_ref_count] = [cus_ref_count] +1 其中 [cus_id] = @ sm_cus_id
结束
-
声明 @ pc_old int
声明 @ pc_new int
选择 @ pc_old = presenter_code 来自客户其中 cus_id = @ sm_cus_id_main
选择 @ pc_new = presenter_code 来自的客户其中 cus_id = @ sm_cus_id
更新客户 set [cus_score] = [cus_score]-@ Customer_Score_old 哪里 [cus_id] = @ pc_old
更新客户设置 [cus_score] = [cus_score] + @ Customer_Score_new 哪里 [cus_id] = @ pc_new
- ########################## ################################################ ###################
- کمکردنمحصولازتعدادکالای1
如果 @ smd_product_id1 <> @ smd_product_id1_main
开始
更新产品集合 [product_count] = [product_count] +1 其中 [product_id] = @ smd_product_id1_main
更新产品集合 [product_count] = [product_count] -1 其中 [product_id] = @ smd_product_id1
结束
- کمکردنمحصولازتعدادکالای2
如果 @ smd_product_id2 <> @ smd_product_id2_main
开始
更新产品集合 [product_count] = [product_count] +1 其中 [product_id] = @ smd_product_id2_main
更新产品集合 [product_count] = [product_count] -1 其中 [product_id] = @ smd_product_id2
结束
- کمکردنمحصولازتعدادکالای3
如果 @ smd_product_id3 <> @ smd_product_id3_main
开始
更新产品集合 [product_count] = [product_count] +1 其中 [product_id] = @ smd_product_id3_main
更新产品集合 [product_count] = [product_count] -1 其中 [product_id] = @ smd_product_id3
结束
- کمکردنمحصولازتعدادکالای4
如果 @ smd_product_id4 <> @ smd_product_id4_main
开始
更新产品集合 [product_count] = [product_count] +1 其中 [product_id] = @ smd_product_id4_main
更新产品集合 [product_count] = [product_count] -1 其中 [product_id] = @ smd_product_id4
结束
- کمکردنمحصولازتعدادکالای5
如果 @ smd_product_id5 <> @ smd_product_id5_main
开始
更新产品集合 [product_count] = [product_count] +1 其中 [product_id] = @ smd_product_id5_main
更新产品集合 [product_count] = [product_count] -1 其中 [product_id] = @ smd_product_id5
结束
- ************************ ****************************************************** *******
返回 1
解决方案
基本上，您有2个选择.你要么
-在过程内提交/回滚事务
-或在呼叫方的外部(客户端应用程序等)
两者都有优点和缺点.如果将事务处理放置在过程内部，则调用两个单独的过程将作为不同的事务处理.另一方面，在外部frmo，您可以在同一事务中进行多个调用.
如果您在过程之外处理事务，则取决于语言如何处理事务.例如，.Net具有 SqlTransaction [如何在sql服务器中提交和回滚事务?
在SQL Server存储过程中管理事务
how do i commit or rollback this statement ?
please Take It For Me
(sorry For My Big Text)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Sell_Main_Update_Ghest]
(
@sm_id_main [bigint],
@sm_cus_id_main [bigint],
@smd_product_id1_main [bigint] ,
@smd_product_id2_main [bigint] ,
@smd_product_id3_main [bigint] ,
@smd_product_id4_main [bigint] ,
@smd_product_id5_main [bigint] ,
@smd_id_main1 [bigint],
@smd_id_main2 [bigint],
@smd_id_main3 [bigint],
@smd_id_main4 [bigint],
@smd_id_main5 [bigint],
@sm_id [bigint],
@sm_cus_id [bigint],
@sm_date [nvarchar](10),
@sm_time [nvarchar](10),
@sm_rebate [bigint] ,
@sm_pardakht [bigint] ,
@smd_id [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] ,
@main_pay_sm_id [bigint],
@pay_ghest_count [bigint],
@pay_ghest_day [bigint],
@pay_sm_id [bigint],
@pay_cus_id [bigint],
@pay_ghest_price [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 exists (select * from [Sell-Main] where [sm_id]=@sm_id and [sm_id]<>@sm_id_main)
return 3
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
if exists (select * from Peyment Where Peyment.[pay_cus_id]=@sm_cus_id_main and [pay_state]='پرداخت شده')
return 9
--*********************************************************************************
Update [Sell-Main]
Set
[sm_id]=@sm_id ,
[sm_cus_id]=@sm_cus_id ,
[sm_date]=@sm_date ,
[sm_time]=@sm_time ,
[sm_rebate]=@sm_rebate ,
[sm_pardakht]=@sm_pardakht
where
[sm_id]=@sm_id_main
--*********************************************************************************
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id
where
[smd_sm_id]=@sm_id_main
--OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY
--OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY
--*********************************************************************************
if @smd_product_id1 <> @smd_product_id1_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id1 ,
[smd_product_price]=@smd_product_price1
where
[smd_id]=@smd_id_main1
end
--else
else if @smd_product_id1=0 and @smd_product_id1_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main1
--Insert Condition
if @smd_product_id1<>0 and @smd_product_id1_main=0
begin
declare @smd_id_number1 int
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
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
--*********************************************************************************
if @smd_product_id2 <> @smd_product_id2_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id2 ,
[smd_product_price]=@smd_product_price2
where
[smd_id]=@smd_id_main2
end
--else
else if @smd_product_id2=0 and @smd_product_id2_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main2
--Insert Condition
if @smd_product_id2<>0 and @smd_product_id2_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
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
--*********************************************************************************
if @smd_product_id3 <> @smd_product_id3_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id3 ,
[smd_product_price]=@smd_product_price3
where
[smd_id]=@smd_id_main3
end
--else
else if @smd_product_id3=0 and @smd_product_id3_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main3
--Insert Condition
if @smd_product_id3<>0 and @smd_product_id3_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
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
--*********************************************************************************
if @smd_product_id4 <> @smd_product_id4_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id4 ,
[smd_product_price]=@smd_product_price4
where
[smd_id]=@smd_id_main4
end
--else
else if @smd_product_id4=0 and @smd_product_id4_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main4
--Insert Condition
if @smd_product_id4<>0 and @smd_product_id4_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
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
--*********************************************************************************
if @smd_product_id5 <> @smd_product_id5_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id5 ,
[smd_product_price]=@smd_product_price5
where
[smd_id]=@smd_id_main5
end
--else
else if @smd_product_id5=0 and @smd_product_id5_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main5
--Insert Condition
if @smd_product_id5<>0 and @smd_product_id5_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
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 @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
--*********************************************************************************
delete from [Peyment] where [pay_sm_id]=@main_pay_sm_id
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
declare @count_checker3 int
select @count_checker3=count(*) from [Peyment]
if @count_checker3<>0
begin
declare @pay_id_number int
select @pay_id_number=max(pay_id) from [Peyment]
end
else
begin
set @pay_id_number=1
end
declare @datetime datetime
select @datetime=getdate()
--DT = Datetime
declare @DT nvarchar(10)
declare @i int
set @i=1
while (@i<=@pay_ghest_count)
begin
select @datetime=dateadd(dd,@pay_ghest_day,@datetime)
SELECT @dt=convert(nvarchar,@datetime,111)
insert into [Peyment]
(
pay_id,
pay_sm_id,
pay_cus_id,
pay_price,
pay_date
)
values
(
@pay_id_number+1,
@pay_sm_id,
@pay_cus_id,
@pay_ghest_price,
@DT
)
set @i=@i+1
select @pay_id_number=max(pay_id) from [Peyment]
end
--****************************************************************
--****************************************************************
declare @pp1 int
set @pp1=0
select @pp1=[sm_pardakht] from [Sell-Main] where [sm_id]=@sm_id_main
--***************************************
declare @Customer_Score_old int
declare @Customer_Score_new int
set @Customer_Score_old=0
set @Customer_Score_new=0
set @Customer_Score_old=@pp1/1000
set @Customer_Score_new=@sm_pardakht/1000
--***************************************
--تغییرات امتیاز و تعداد خرید مشتری قدیم و جدید
if @sm_cus_id_main <> @sm_cus_id
begin
update Customers set [cus_score]=[cus_score]-@Customer_Score_old ,[cus_sell_count]=[cus_sell_count]-1, [cus_ref_count]=[cus_ref_count]-1 where [cus_id]=@sm_cus_id_main
update Customers set [cus_score]=[cus_score]+@Customer_Score_new ,[cus_sell_count]=[cus_sell_count]+1 , [cus_ref_count]=[cus_ref_count]+1 where [cus_id]=@sm_cus_id
end
--اضافه و کم کردن امتیاز به معرف قدیم و جدید
declare @pc_old int
declare @pc_new int
select @pc_old=presenter_code from customers where cus_id=@sm_cus_id_main
select @pc_new=presenter_code from customers where cus_id=@sm_cus_id
update Customers set [cus_score]=[cus_score]-@Customer_Score_old where [cus_id]=@pc_old
update Customers set [cus_score]=[cus_score]+@Customer_Score_new where [cus_id]=@pc_new
--#############################################################################################
-- کم کردن محصول از تعداد کالای 1
if @smd_product_id1 <> @smd_product_id1_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id1_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id1
end
-- کم کردن محصول از تعداد کالای 2
if @smd_product_id2 <> @smd_product_id2_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id2_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id2
end
-- کم کردن محصول از تعداد کالای 3
if @smd_product_id3 <> @smd_product_id3_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id3_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id3
end
-- کم کردن محصول از تعداد کالای 4
if @smd_product_id4 <> @smd_product_id4_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id4_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id4
end
-- کم کردن محصول از تعداد کالای 5
if @smd_product_id5 <> @smd_product_id5_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id5_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id5
end
--*********************************************************************************
return 1 解决方案
Basically you have 2 choices. You either
- commit/rollback the transaction inside the procedure
- or outside on the calling side (client app etc)
Both have pros and cons. If you place the transaction handling inside the procedure, calling two separate procedures are handled as different transactions. On the other hand frmo outside, you can have multiple calls inside the same transaction.
If you handle the transaction outside the procedure, it depends on the language how the transaction is handled. For example .Net has SqlTransaction[^] along with other techniques.
If you handle the transaction inside, the place a BEGIN TRANSACTION in the beginning. After that I prefer to have only one COMMIT in the end for succesful execution and ROLLBACKS are placed on every exit for errors.
Try following:
How to commit and rollback transaction in sql server?
Managing Transactions in SQL Server Stored Procedures
这篇关于如何提交或回滚此声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
