By: Tim Smith | Updated: 2016-10-12 | Comments | Related: 1 | 2 | > Stored Procedures
Problem
We have a default set of values that we might want to use for most transactions, but there are cases where different values will need to be used which will be on a case-by-case basis. We do make adjustments to our default set of values sometimes, so this won't always be set to the same values. What are some ways to handle default value sets that also allow for updates if and when needed?
Solution
We have many ways to approach a solution with this and in this tip we'll look at a few of them. In this tip's example, we'll assume that we sell a combination of grocery items to various stores, and the stores that are close receive a default price, while stores further away are charged on the basis of the cost of their distance. Because the price of our grocery items may change, our default price will change in these circumstances, while we can treat other transactions outside the default on a transaction-by-transaction basis or based on a calculated derivative of the default price.
We could directly hard code the default value in an object, like a stored procedure, or on a line of code. This provides the least flexibility, if the other methods in this tip have an interface where the default can be adjusted. In some cases, however, this could make sense. For a simple example of this, if our product price has a default price limit set by a third party and the price can never exceed that value, we might use the default as the limit price for a procedure variable. If, however, the limit price changes, the procedure must be updated.
---- DROP PROCEDURE stp_ReturnPriceEggs CREATE PROCEDURE stp_ReturnPriceEggs @quantity INT , @price DECIMAL(13,2) = NULL AS BEGIN IF @price IS NULL BEGIN ---- If 2.59 was the maximum price, we could set a manual limit here: SELECT (@quantity*2.59) AS CostPerDozen END ELSE BEGIN SELECT (@quantity*@price) AS CostPerDozen END END
Here are examples how this can be called:
-- use the default price EXEC stp_ReturnPriceEggs 10 -- use a price of 2.25 EXEC stp_ReturnPriceEggs 10,2.25 -- use a price of 1.75 EXEC stp_ReturnPriceEggs 10,1.75
This approach can work without issues until the default limit is changed, depending on how it's used. The work required to make the change may also not seem like an issue if the environment is small, or if this is intended as a user-capture program. In general, I only use this approach on base unit calculations where I might want to change the base unit, like measuring gold in grams instead of ounces, even though ounces is my set default. However, for transactions for orders, this creates more problems in that it lacks flexibility and limits tracking orders.
Suppose that we have three products - eggs, whole milk and sharp cheddar - and we charge a default value for some customers, while we charge a case-by-case price for other customers. One way we can organize this is to set a default value in the first transaction identifying row (TransactionID in the below example) and update that one row when the default changes. In the below example, our stored procedure uses a default parameter to determine whether to add new values and return them, or return the default value (or another TransactionID value):
---- Example: ---- DROP TABLE tblColdFood CREATE TABLE tblColdFood( TransactionID INT IDENTITY(1,1), FoodEggs DECIMAL(13,2), FoodWholeMilk DECIMAL(13,2), FoodSharpCheddar DECIMAL(13,2) ) INSERT INTO tblColdFood (FoodEggs,FoodWholeMilk,FoodSharpCheddar) VALUES (2.5,3,1.25) ---- DROP PROCEDURE stp_ReturnPriceColdFood CREATE PROCEDURE stp_ReturnPriceColdFood @priceeggs DECIMAL(13,2) = NULL , @pricewholemilk DECIMAL(13,2) = NULL , @pricesharpcheddar DECIMAL(13,2) = NULL , @tid INT = NULL AS BEGIN IF @tid IS NULL BEGIN INSERT INTO tblColdFood (FoodEggs,FoodWholeMilk,FoodSharpCheddar) VALUES (@priceeggs,@pricewholemilk,@pricesharpcheddar) SELECT @priceeggs AS FoodEggs , @pricewholemilk AS FoodWholeMilk , @pricesharpcheddar AS FoodSharpCheddar END ELSE BEGIN SELECT FoodEggs , FoodWholeMilk , FoodSharpCheddar FROM tblColdFood WHERE TransactionID = @tid END END
Here are example calls:
-- use the default prices where tid = 1 EXEC stp_ReturnPriceColdFood @tid = 1 -- use these prices instead EXEC stp_ReturnPriceColdFood @priceeggs = 3 , @pricewholemilk = 4.5 , @pricesharpcheddar = 3 -- we could also create a new set of default prices where tid = 2 SELECT * FROM tblColdFood INSERT INTO tblColdFood (FoodEggs,FoodWholeMilk,FoodSharpCheddar) VALUES (2.75,3.25,1.50) EXEC stp_ReturnPriceColdFood @tid = 2
While this carries advantages over the first approach in that an interface might make it easier to reset the default outside of coding, if we add products to our inventory, we would then need to add columns to the table and thus update database and application code. This approach would only make sense in the context of a fixed category set that cannot be expanded (or is highly unlikely to change) and where a history is required, where the identifying row could be tied specifically to a date (since a specific TransactionID in the above example could be used more than once, the date field would exist in another table).
Finally, the preferred approach for this situation in the case of applications: the case-by-case basis is determined by a calculated derivative of the default price is to use the default price as the standard and on those cases, calculate the price using algorithms. If the algorithms are flexible enough in that they allow the necessary parameters - like miles and gasoline prices in our example - we can simply store those variables in other tables by change and call them in our algorithm. Consider the example of deriving a price where we charge the customer $0.25 per ten miles if the price of gasoline is between $2.00 and $2.50 in addition to the default price. In this example, we could derive the price charged based on what we have saved in our table structure, such as the price of gasoline by day, the mileage distant of the customer, etc. This also allows us to make the necessary joins when we query the history, provided that we have a history of the default prices when they change. In some cases, it might make sense to save a price history of every order if default price histories are not retained.
While we are using a simple example of grocery items, the downside to this approach is that for some applications it may require ETL loaders to capture data for reporting purposes - like capturing the price of gasoline for reports so that we don't have to manually enter it. This isn't necessarily a problem, but it may require additional skill depending on the complexity of what we need. Generally, smaller environments may prefer a manual entry because orders might only be once a week and building an ETL for their needs is overkill. In addition, if our pricing structure for our derived tables changes, then we must update those tables as well as defaults - all tables involved in a possible change must be updated. And for data capture applications, if we're just trying to capture how our users are using algorithms available to them, we might not need an entire architecture for capturing these data.
This tip offers a few approaches to handling default values, if needed. Many other approaches also exist and ultimately customer use and client need determine the best way to proceed. I suggest keeping an eye on maintenance costs and complexity, such as tracking what will need to change and how can that be simplified.
Next Steps
- Consider situations where each have advantages and disadvantages, along with other approaches and where you'd use those.
- If defaults must change - along with other dependencies to those defaults - what architecture makes it easier to identify the values that must change?
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-10-12
