August 13, 2008 at 11:07 am
I'm trying to determine the best way to write a stored procedure.
We have downloads that are "token based", meaning that a download costs a predetermined number of tokens and not a specific dollar amount.
I have a procedure that runs, called "ProcessTokenOrders", this procedure takes all of the orders from the shopping cart that contain tokens and add them to a table called "DownloadOrders". The structure for DownloadOrders is below:
OrderNumber int,
CustomerID int,
NumTokens int,
IsProcessed bit
I have another table called "TokenManager", this table contains the current user token balance. Below is the structure:
CustomerID int,
NumTokens int
I want the new procedure to look at the TokenManager table, and determine if there is a record for that CustomerID in the table already, if there is, it should add the SUM of the tokens orders to the current balance. If no record exists, then a new record should be created with the SUM of the tokens orders. Finally, after all of the records have been updated, I want the IsProcessed flag to be set to true.
The best method that I've come up with so far has been to loop through all of the CustomerID's and call a procedure called "UpdateTokenManager", which accepts CustomerID and NumTokens as variables, then it checks for a record and does an INSERT or UPDATE depending on if the record exists or not.
Thanks in advance for any help.
Stephen
August 13, 2008 at 11:20 am
well, first,
I've always tried to get rid of any tables that were trying to hold summary data with a view. it does away with needless updating.
i think based on what you've described you can get rid of the table TokenManager and replace it with a view:
SELECT CustomerID SUM(NumTokens) AS NumTokens FROM DownloadOrders WHERE IsProcessed = 0 GROUP BY CustomerID
that gives you all the "unprocessed" tokens right? and as a view never needs to be updated.
Next your procedure ProcessTokenOrders simply add the order, and then do a simple update:
UPDATE DownloadOrders SET IsProcessed =1 WHERE CustomerID =[the id you are processing]
that will automatically update the view.I'm assuming you are processing one order at a time, but maybe i need more information.
Lowell
August 13, 2008 at 11:24 am
it might be interesting to find data in the table that does not match it's supposed details:
does this return anything?
SELECT * FROM TokenManager
FULL OUTER JOIN
(SELECT CustomerID SUM(NumTokens) AS NumTokens FROM DownloadOrders WHERE IsProcessed = 0 GROUP BY CustomerID ) X ON TokenManager.CustomerID = X.CustomerID
WHERE ISNULL(TokenManager.NumTokens) <> X.NumTokens
Lowell
August 13, 2008 at 1:06 pm
I don't think I'd be able to use a view in this case, reason being, I need a way to track token transactions, example being reducing the amount of tokens that are used or added outside of the store (Customer Service and/or Promotional purposes).
I also want to make sure I can keep track of line items per customer for auditing purposes. So, If a user buys 3 tokens, but is given 3 more by Customer Service, and then uses 2, I want to be able to see all of those transactions and not just the current end result.
Thanks.
Stephen
August 13, 2008 at 1:53 pm
I think you'll still get that ability with what Lowell's suggesting. Your token transaction history will still be kept in the DownloadOrders table. The view simply queries the DownloadOrders table and gives each user's current token balance. From what you've stated, this should work. If you don't think so, can you post some sample data for both tables to show what you want to see happen?
August 13, 2008 at 2:08 pm
You're right. I think I misunderstood when I initially read it.
I don't have too much experience using views. How to I account for NULL?
The way I'm doing it now, I get the SUM for the NumTokens, and set it to @NumTokens, then I check if the value of @NumTokens is NULL using and IF statement, and if so, set the value = 0.
Should I use a CASE statement? Or is there a better way?
Thanks.
Stephen
August 13, 2008 at 2:36 pm
August 13, 2008 at 2:38 pm
Actually, it depends on what you are doing. If you are using SUM(), NULLs will be handled for you. Here's a great link for you to read:
http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/
August 13, 2008 at 3:40 pm
Stephen Lee (8/13/2008)
I don't think I'd be able to use a view in this case, reason being, I need a way to track token transactions, example being reducing the amount of tokens that are used or added outside of the store (Customer Service and/or Promotional purposes).I also want to make sure I can keep track of line items per customer for auditing purposes. So, If a user buys 3 tokens, but is given 3 more by Customer Service, and then uses 2, I want to be able to see all of those transactions and not just the current end result.
Thanks.
Stephen
I believe you'll still b able to use views, it's just they might be a bit more complex;
for example, you might have two or three detail tables, where you'd be trying to do something like this:
SELECT
CustomerID,CustomerServicePromotions.NumTokens
+ DownloadOrders.NumTokens
- PaidOrders.NumTokens
you could easily create a view which has the details, or rolls up all the data; I'm just throwing out an example, but it might get the wheels turning:
SELECT X.CustomerID,((BonusTokens + NumTokens ) -UsedTokens ) As LeftOverTokens
From
(SELECT CustomerID, SUM(BonusTokens) AS BonusTokens
FROM CustomerServicePromotions
Group By CustomerID) X
Left Outer Join
(SELECT CustomerID, SUM(NumTokens) AS NumTokens
FROM DownloadOrders
Group By CustomerID ) Y on X.CustomerID = Y.CustomerID
Left Outer Join
(SELECT CustomerID, SUM(UsedTokens) AS UsedTokens
FROM PaidOrders
Group By CustomerID ) Z on Y.CustomerID = Z.CustomerID
Lowell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply