We help IT Professionals succeed at work.

SQL SP Logic - Fresh Eyes Please

488 Views
Last Modified: 2012-02-13
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

Open in new window

Comment
Watch Question

SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.