how do commit or rollback this statments

Question

0.00/5 (No votes)

See more:

how do i commit or rollback this statement ?

please Take It For Me

(sorry For My Big Text)

SQL

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

Posted 17-May-12 8:32am

Esmaeil Ebrahimi

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-05-17T08:40:00

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.

Monjurul Habib · Accepted Answer · 2012-05-17T09:06:00

Solution 2

Try following:

How to commit and rollback transaction in sql server?
Managing Transactions in SQL Server Stored Procedures

Posted 17-May-12 9:06am

Monjurul Habib

Updated 17-May-12 9:07am

v2

Comments

Wendelius 17-May-12 15:20pm

Good links!

Monjurul Habib 17-May-12 15:25pm

thanks :)

how do commit or rollback this statments

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

how do commit or rollback this statments

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us