The Baker's Dozen: 13 Productivity Tips for Database Development Using Transact-SQL

Many 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 possibilities. This installment of "The Baker's Dozen" presents a variety of real-world database situations and how you can use Transact-SQL and SQL-92 to tackle these issues.

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:

• 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 JOINs
• Use LIKE to build text-search routines
• Understand and use CASE syntax to evaluate run-time conditions
• Baker's Dozen Spotlight: 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.

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.

Listing 1 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:

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.

By: Kevin S Goff

Kevin S. Goff, a Microsoft MVP award recipient for 2007, is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom Web and desktop software solutions in .NET, VFP, SQL Server, and Crystal Reports. Kevin is the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. Kevin has been building software applications since 1988. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 Companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training. Contact Kevin at kgoff@commongroundsolutions.net

kgoff@commongroundsolutions.net