SQL SP Logic - Fresh Eyes Please

Morning SQL Experts,,

Grateful if you could cast a quick eye over my Stored Procedure, and let me know if my logic is sound - or of course suggest any improvements...

This is the process -

The SP is sent two variables; OrderID & ProductID,

Firstly it -

Selects the Product Information from the Product table (dbo.[Direct-Product]) Where the ProductID matches

It generates a new stockcount value (Current stockcount - 1)

Next it inserts a row into the Order Table(dbo.OrderDetail) using the OrderID, ProductID and data selected from the initial select statement.

Next it updates the stockcount in the Product table

Next it declares a variable TotalPrice which is the sum on the OrderDetail.TotalPrice for all rows where OrderDetail.OrderID = OrderID

(***** dbo.OrderDetail stores individual products ordered whereas dbo.Ordertb stores the Order Overview - Total Cost / Customer / Date etc)

Now it updates the dbo.Ordertb.OrderSubTotal with the TotalPrice variable for the record with a matching OrderID

So far so good??

Next it determines who the customer is by Selecting PartnerID from the Ordertb table where OrderID matches.

Once it knows the ID of the Partner it determines whether the Partner (customer) is VAT liable by decalaring a variable @VatLiable which is the value of dbo.member.[VAT-Liable] where the ID matches the PartnerID.

Next it declares a variable @VatRate which selects the current VAT Rate from the tax table.

Finally -

IF

@VatLiable = 'Y'

It updates the dbo.ordertb.OrderTax column with the following calculation -

SUM(SUM(SUM(OrderSubTotal + ISNULL(OrderDeliveryCharge,0))- ISNULL(OrderDiscount,0))*@VatRate)
....

I Hope my logic makes sense.... please excuse the member / partner tables - these are legacy tables - I'm stuck with the names of these...

Here is my code - grateful for thoughts & suggestions -

Cheers -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[usp_AdminAddOrderItem]
@OrderID INT,
@ProductID INT
AS
BEGIN
declare @ProductName Nvarchar(50)
SET @ProductName = (Select [Product-Name] from dbo.[Direct-Product] where ID = @ProductID)

declare @ProductSKU Nvarchar(10)
SET @ProductSKU = (Select [SKUCode] from dbo.[Direct-Product] where ID = @ProductID)

declare @ProductPrice Money
SET @ProductPrice = (Select Case When P.[Display-TGI-Special-Price]='Y' then DP.[Special-Price] Else DP.[Normal-Price] end AS Price from dbo.[Direct-Product] P inner join dbo.[Direct-Product-Price] DP on DP.[Product-ID] = P.ID where P.ID = @ProductID)

Declare @StockCount INT
SET @StockCount= (Select StockCount from dbo.[Direct-Product] where ID = @ProductID)

Declare @NewStockCount INT
Set @NewStockCount = SUM(@StockCount - 1)

insert into dbo.OrderDetail(OrderID, ProductID, ProductName, ProductSKU, Price, Quantity, TotalPrice, Status)
Values (@OrderID, @ProductID, @ProductName, @ProductSKU, @ProductPrice, 1, @ProductPrice, 1)

Update dbo.[Direct-Product]
Set StockCount = @NewStockCount where ID = @ProductID

Declare @TotalOrderPrice Money
SET @TotalOrderPrice = (SELECT sum(TotalPrice) FROM OrderDetail where OrderID = @OrderID)

UPDATE dbo.Ordertb
SET OrderSubTotal = @TotalOrderPrice 
WHERE OrderID = @OrderID

Declare @PartnerID INT
SET @PartnerID = (select MemberID from dbo.Ordertb where OrderID = @OrderID)

Declare @VatLiable char(1)
Set @VatLiable = (Select [VAT-Liable] from dbo.Member where ID = @PartnerID)

Declare @VatRate DECIMAL(5,2)
Set @VatRate = (Select VATRate from dbo.Tax)

IF @VatLiable = 'Y'

Update dbo.Ordertb
Set OrderTax = SUM(SUM(SUM(OrderSubTotal + ISNULL(OrderDeliveryCharge,0))- ISNULL(OrderDiscount,0))*@VatRate)

END
GO

Select allOpen in new window