Re: transaction in sp or front end

TATWORTH — Sat, 04 Jul 2009 17:37:57 GMT

>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.

Re: transaction in sp or front end

RickNZ — Sat, 04 Jul 2009 00:24:19 GMT

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

Re: transaction in sp or front end

Naom — Fri, 03 Jul 2009 15:36:19 GMT

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

transaction in sp or front end

sparrow37 — Fri, 03 Jul 2009 08:18:09 GMT

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)

)

/* 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

Regards,

Asif Hameed

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)

)

/* 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

SQL Server, SQL Server Express, and SqlDataSource Control

Re: transaction in sp or front end

Re: transaction in sp or front end

Re: transaction in sp or front end

transaction in sp or front end