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

I would use the transaction right in this SP using BEGIN TRANSACTION / COMMIT TRANSACTION commands.

I used this approach myself. Here is a sample http://forums.asp.net/p/1373630/2880551.aspx#2880551

I prefer a slightly different pattern than Naom's (although it will also work fine).  The raiserror code should be moved to its own SP.  The key difference is the use of xact_abort and xact_state():

CREATE procedure [dbo].[YourProc]
as
begin
	set nocount on
	set xact_abort off
	begin try
		begin transaction
                   -- your code here
		commit transaction
	end try
	begin catch
		if xact_state() <> 0
		begin
			-- uncommittable transaction
			rollback transaction
		end
	        declare @ErrorMessage nvarchar(4000)
	        declare @ErrorSeverity int
	        declare @ErrorState int
	        set @ErrorMessage =
                    convert(varchar(10), error_number()) + ':' + error_message()
	        set @ErrorSeverity = error_severity()
	        set @ErrorState = error_state()
	        if @ErrorState = 0
		set @ErrorState = 1
	        raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
	end catch
end

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

Wherever possible the transaction should be in the database. With SQL 2008 the classical exception of the order header and variable number of detail lines no longer applies as a stored procedure can be passed an array.