transaction in sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

transaction in sp

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-03 : 04:17:55

Hi all,

I want to use transaction in following sp so that all steps happen or nothing happen.

create order
create order detail
delete items from shopping cart after adding item in order and order detail

How to use transaction. any better solution

ALTER PROCEDURE [dbo].[CreateCustomerOrder]
(
@CustomerName VARCHAR(101),
@ShippingRegion int, -- 1 domestice 2 international
@ShipmentType int, -- 2 2day /3 ground /1 nextday
@PaymentType int, -- 1 paypal , 2 CC
@CustomerEmail VARCHAR(100),
@CustomerID int,
@OrderStatus int,
@TransID VARCHAR(50), -- 0 cancel / 1 complete / 2 pending / 3 verified
@ShippingCost float,
@Discount float,
@Tax float, -- not needed here
@isWholesaler bit, /* it can be checked using select iswholesaler from users where userid = custid*/
@CCNumber varchar(1000),
@CreatedBy varchar(50)
)
AS

/* Insert a new record INTo Orders */
DECLARE @OrderID INT

INSERT INTO ORDERS(CustomerName,ShippingRegion,ShipmentType,PaymentType,CustomerEmail,CustomerID,OrderStatus,TranscID,ShippingCost,Discount,Tax,CCNumber,IsActive,CreatedBy,CreatedOn)
VALUES(@CustomerName,@ShippingRegion,@ShipmentType,@PaymentType,@CustomerEmail,@CustomerID,@OrderStatus,@TransID,@ShippingCost,@Discount,@Tax,@CCNumber,1,@CreatedBy,getdate())

/* Save the new Order ID */
SET @OrderID = scope_IDENTITY()

/* Add the order details to OrderDetail */
INSERT INTO OrderDetails (OrderID, ProductID, ProductName, Quantity, UnitCost)
SELECT @OrderID, p.ProductID, p.ProductName, s.Quantity,

case when @isWholesaler = 1 then UnitPrice_WholeSale
when onsale= 0 then p.UnitPrice_retail
when onsale = 1 then p.SalePrice end unitprice

FROM Products p JOIN ShoppingCart s
ON p.ProductID = s.ProductID
WHERE s.CreatedBy = @CustomerID

/* Clear the shopping cart */
DELETE FROM ShoppingCart
WHERE CreatedBy = @CustomerID
/* Return the Order ID */
SELECT @OrderID

should i use transcation in db or front end. should i return some value based upon success or failure

Regards,
Asif Hameed

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 04:20:47

Hi
You can use Try catch block
example:--
DECLARE @intErrorCode INT

BEGIN TRAN
UPDATE Authors
SET Phone = '415 354-9866'
WHERE au_id = '724-80-9391'

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

UPDATE Publishers
SET city = 'Calcutta', country = 'India'
WHERE pub_id = '9999'

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-03 : 05:46:29

what if i use transaction. In above case, if one statement fails all will fail ?

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 06:20:11

all will not fail

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-03 : 06:42:47

Your are best putting this in a Begin Try, which if at anypoint it fails then the begin try will start, better that trying to do the error catching in seperate if statements

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 06:46:39

SQL SERVER 2000 its possible to use Begin Try catch block

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-03 : 07:57:28

any example please

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 08:04:37

BEGIN TRY
BEGIN TRAN

SELECT 'success'as Result
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT 'failure'as Result

END CATCH