any application developers face the challenges of working with SQL Server 2000. These challenges include retrieving and grouping large amounts of data, building result sets, and tracking changes to data. All require professional strategies that hold water against a seemingly endless number of possibilitiesand tips can help. Here's a sampling of T-SQL tips for new and veteran developers.
Beginning with the End in Mind
A major objective of "The Baker's Dozen" is to provide tips for those wishing to become more productive in a certain technology. Hopefully, even those with experience may still find a gold nugget in one of the tips. Just like the legendary Van Amsterdam Bakery that gave us the original Baker's Dozen name, I've spent the last few months baking a batch of tasty tips to serve up to developers, in hopes of helping you meet the challenges of SQL Server 2000.
Most of the tips raise a business application requirement, and address how you can use Transact-SQL code to solve it. The tips in this issue are:
Baker's Dozen Spotlight
- Return multiple result sets from a single stored procedure.
- Write User-Defined-Functions (UDFs) to return a scalar value.
- Write a UDF to parse a comma-separated list of selection keys and return a table that can be used for subsequent JOIN statements.
- Use LIKE to build text-search routines.
- Understand and use CASE syntax to evaluate run-time conditions
- Use subqueries and derived tables to handle involved requirements, and a brief discussion about different techniques
- Use table variables, and understand the differences between table variables and temporary tables
- Use datepart functions to summarize daily data by a week-ending date
- Learn how to use functions like CAST and CONVERT to make different data types work together
- Use triggers to define specific actions when database row(s) are modified
- Extend triggers to implement basic audit trail capability
- Use Dynamic SQL to build queries where key conditions are not known until runtime
- Retrieve a list of tables and columns from a particular database
You can find all listings in this article on the Common Ground Solutions Web site at www.commongroundsolutions.net. So once again, borrowing from the theme of Van Amsterdam's bakery, here are thirteen productivity tips, a "Baker's Dozen".
Tip 1: Return Multiple Result Sets
Requirement: You need to create a simple stored procedure to return all the Order Information for a single customer. The result set must contain three tables: the order header, order detail, and order notes.
|Developers of similar strength and experience can and will disagree on the emphasis of performance (or the promise of performance) versus maintenance and readability. Sometimes there is no single correct answer. The key is to apply consistency in approaching these types of situations.|
A single SQL 2000 stored procedure can easily return multiple result sets. The general rule is that any SQL SELECT statement in a stored procedure with no explicit output declaration (to a table, variable, etc.) returns the results to the calling routine.
shows a basic stored procedure that contains one argument, an integer key for a customer account. The procedure queries the Order Header, Detail, and Notes tables. Note that the procedure creates a variable of type table
, and stores the list of order keys to be used for all three queries. Tip #7 covers table variables in more detail.
Because the three queries do not contain an output destination, the results are returned to the calling procedure. You can execute the stored procedure from a script in Query Analyzer, which displays the three results sets when the query runs. If you called the stored procedure from C#, the data adapter fills the result set (dataset) with the three data tables, like so:
SqlDataAdapter oDa =
new SqlDataAdapter(cSQLString, oMyConn);
// Table 0 will contain the order headers
// Table 1 will contain the order details
// Table 2 will contain the order notes
Normally, you would use some type of data access methodology to call stored procedures. A future Baker's Dozen article will discuss these types of development strategies in a distributed computing environment.
Tip 2: UDFs to Return a Value
Requirement: You need to sum customer debits and credits based on a cutoff date, and return the sum as a customer balance. You would like to use this calculation across multiple queries
|This is another example where you can use Transact SQL to prepare a result set with little or no data munging in the business layer.|
SQL 2000 allows developers to write and call user-defined functions. UDFs allow developers to encapsulate logic and functionality, and return a scalar value that they can use across different queries. Listing 2
presents a basic UDF that sums a table of debits and credits for a customer and date threshold, and returns the result. You can use the function in a query as follows:
DECLARE @dCutOffDate DATETIME
SET @dCutOffDate = CAST('11/30/2004' AS DateTime)
SELECT CustID, dbo.CUSTBAL(CustID, @dCutOffDate)
If the application has five queries that need the customer balance, your code can use the UDF every time, instead of repeating the same code across every query.
Of course, that is a basic demonstration of UDFs. Now I'll add a twist. The Customer Balance UDF queries the Debits
tables based on a COMPDATE
column. Suppose the Debits
tables contain three dates that represent the different stages of a record (DRDATE
for draft, WIPDATE
for work-in-progress, and COMPDATE
for completed date). The balance UDF needs to examine the record status to determine the date to which the cutoff date should be compared.
The balance UDF could implement a series of in-line IF
statements to read the status and use the corresponding date column. However, other processes may need this logic. Listing 3
demonstrates a second UDF that retrieves the customer record and returns the appropriate date, given the customer's status. Now, instead of saying:
AND CompDate <= @dCutOffDate
You can use the following:
Of course, this means that the original query calls a UDF which in turn calls another UDF. The interest in isolating code should be weighed against performance. If no other queries/functions use this logic to determine the date, then perhaps this second UDF is not necessary. You should make this decision after identifying all the technical requirements of the application.
Please note that SQL Server 2000 UDFs carry some restrictions. You cannot modify data in any existing tables inside a UDF, nor can you create temporary tables inside a UDF. The general purpose of a UDF is to leverage code to return results, not to modify data.