SQL Transaction Return Value

[hooks]

Hey all. Im back with another SQL question

I am using the below stored procedure to insert a new order for a customer.
It also moved items from the customers shopping cart to the order details table.

The procedure is working great with the exception that i am not getting the OrderID return value. Everything esle is working.

Im using SQL server 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[InsertOrder]
(
	@CustomerID				Int,
	@PaymentTypeID			Int,
	@PickupLocationID		Int,
	@NumberOfPayrollDeducts	Int
)
 AS

BEGIN TRANSACTION
	Set NoCount On
	Declare @OrderID Int

-- INSERT the new Order
INSERT INTO ScripSaleOrders(CustomerID, PaymentTypeID, PickupLocationID, NumberOfPayrollDeducts)
VALUES(@CustomerID, @PaymentTypeID, @PickupLocationID, @NumberOfPayrollDeducts)

-- Now return the OrderID of the newly inserted record
Select @OrderID = SCOPE_IDENTITY()

IF @@ERROR <>0
   	BEGIN
   	 -- Rollback the transaction
    	ROLLBACK

   	 -- Raise an error and return
    	RAISERROR ('Error in Inserting Order.', 16, 1)
    	RETURN
 END

-- INSERT the new OrderDetails
INSERT INTO ScripSaleOrderDetails(OrderID, ProductID, Quantity)
SELECT @OrderID, ProductID, Quantity
FROM ScripSaleCart
WHERE CustomerID = @CustomerID

IF @@ERROR <>0
   	BEGIN
   	 -- Rollback the transaction
    	ROLLBACK

   	 -- Raise an error and return
    	RAISERROR ('Error in Inserting OrderDetails.', 16, 1)
    	RETURN
 END

-- Remove  the Shopping Cart for this customer
DELETE FROM ScripSaleCart
WHERE CustomerID = @CustomerID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in Removing Cart.', 16, 1)
    RETURN
 END

-- The commands completed successfully
-- Commit the transaction....
COMMIT
Return @OrderID

As always Thanks a bunch.

 

[Kodo]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[InsertOrder]
(
@CustomerID Int,
@PaymentTypeID Int,
@PickupLocationID Int,
@NumberOfPayrollDeducts Int
)
AS

BEGIN TRANSACTION
Set NoCount On
Declare @OrderID Int

-- INSERT the new Order
INSERT INTO ScripSaleOrders(CustomerID, PaymentTypeID, PickupLocationID, NumberOfPayrollDeducts)
VALUES(@CustomerID, @PaymentTypeID, @PickupLocationID, @NumberOfPayrollDeducts)

-- Now return the OrderID of the newly inserted record
Set @OrderID = SCOPE_IDENTITY()

IF @@ERROR <>0
BEGIN
-- Rollback the transaction
ROLLBACK

-- Raise an error and return
RAISERROR ('Error in Inserting Order.', 16, 1)
RETURN
END

-- INSERT the new OrderDetails
INSERT INTO ScripSaleOrderDetails(OrderID, ProductID, Quantity)
SELECT @OrderID, ProductID, Quantity
FROM ScripSaleCart
WHERE CustomerID = @CustomerID

IF @@ERROR <>0
BEGIN
-- Rollback the transaction
ROLLBACK

-- Raise an error and return
RAISERROR ('Error in Inserting OrderDetails.', 16, 1)
RETURN
END

-- Remove the Shopping Cart for this customer
DELETE FROM ScripSaleCart
WHERE CustomerID = @CustomerID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK

-- Raise an error and return
RAISERROR ('Error in Removing Cart.', 16, 1)
RETURN
END

-- The commands completed successfully
-- Commit the transaction....
COMMIT
Select @OrderID