چطور این دستورات رو با هم اجرا بکنم ... ؟
منظورم اینه که یا همه اجرا بشن (Commit ) ... یا هیچ کدوم اجرا نشن (Rollback)
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
