troubleshooting Question

SQL SP Logic - Fresh Eyes Please

Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
2 Comments1 Solution530 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros