By:Tim Smith || Related Tips:More >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 (@[email protected]) 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 app

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerDUASBTI
分页:12
转载请注明
本文标题:SQL Server T-SQL Stored Procedure Design to Handle Varying Default Values
本站链接:http://www.codesec.net/view/482710.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(37)