Flawed SQL Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Flawed SQL Procedure

Author

Topic

paradise_wolf
Starting Member

32 Posts

Posted - 2007-03-02 : 16:57:27

I am using the below procedure to set the field “Completed” to “True” in the table “Orders”:
~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE SetOrderToCompleted
(@UserName VARCHAR(50))
AS
UPDATE Orders
SET Completed = 1
WHERE UserName = @UserName
AND Completed = 0
~~~~~~~~~~~~~~~~~~~~~~~~~
Which is obviously flawed because I predict a situation where the same customer ( user1 ) could have two different orders, like in the below example, when this procedure will set incorrectly the both fields “Completed” to “True” ( in table Orders ) for OrderID = 1 and OrderID = 2 when actually the not-downloadable product “gadget105” was not received yet by the customer ( Received = False in table “OrderDetails” ).

Observations:

1) Downloadable products like software have their field “Received” set to NULL because they do not need to be shipped and therefore completing this field is irrelevant.

2) Both orders ( OrderID = 1 and 2 ) were made by the same customer with UserName = “user1”.

Table OrderDetails
_______________________________________________________________
OrderID ProductID ProductName Downloadable Quantity Received UnitCost

1 10 software10 True 1 NULL 15.00
1 101 gadget101 False 1 True 20.00
2 12 software12 True 1 NULL 16.00
2 105 gadget105 False 1 False 22.00
2 13 software13 True 1 NULL 22.00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Table Orders
_______________________________________________________________
OrderID UserName PaymentConfirmed Completed
1 user1 True False
2 user1 True False
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(The posting box messes up the original order in the tables.
I hope that my post is still intelligible )

How to solve the problem ?

paradise_wolf
Starting Member

32 Posts

Posted - 2007-03-02 : 17:30:43

Oops, I forgot to add two more observations:

3) I am using the above procedure to set the field “Completed” to “True” in the table “Orders” only when the customer have paid and received or downloaded all his products.

4) The above procedure is only executed after all the downloadable products of the order have being downloaded by the customer and another procedure verified/confirmed that.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-03-02 : 17:34:02

Your procedure should take as input the key of the table, not userID

ALTER PROCEDURE SetOrderToCompleted
(@OrderID INT)
AS
SET NOCOUNT ON
UPDATE Orders
SET Completed = 1
WHERE OrderID = @OrderID

rockmoose

paradise_wolf
Starting Member

32 Posts

Posted - 2007-03-02 : 17:54:46

Hi rockmoose

I cannot use the OrderID because the same customer can have several different orders in the table.

Some orders could have their products received by the customer and others =not=.

I want a procedure that finds out which orders have all their products received by the customer ( downloaded or delivered ) and then set the field "Completed" to True.

paradise_wolf
Starting Member

32 Posts

Posted - 2007-03-02 : 18:57:58

I can obtain the information that all downloadable items were downloaded by the client by verifing the field “RemoveRole” in the CustomerDownload table ( not shown here ) set to ‘yes’.

Based on that, I devised this new procedure but since I am not good with “INNER JOINs”, can somebody tell me if it is correct ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURE SetOrderToCompleted
(@UserName VARCHAR(50))
AS
UPDATE Orders
SET Completed = 1
WHERE OrderID = ( SELECT OrderID
FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID
INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID
WHERE Orders.UserName = @UserName
AND CustomerDownload.RemoveRole=’yes’
AND OrderDetails.Received = 1
AND Orders.Completed = 0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In this procedure I aim to set the field Completed to True for all the customer’s orders that satisfy the above conditions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources