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 SQL Server MVP, has been writing “The Baker’s Dozen” productivity series in CoDe Magazine since 2004. Kevin hosts a weekly webcast on SQL Server and Business Intelligence topics, at http://www.BDBIRadio.com.

Kevin has been an applications developer, architect, and technology mentor since 1987. Kevin has worked in many lines of business, including insurance, manufacturing, health care, consumer packaged goods, accounting and finance, advertising, and many others. Along the way, Kevin has won several awards in both the public and private sector for applications development. Additionally, Kevin has taught SQL Server, Business Intelligence, and Data Warehousing classes to hundreds of students over a period of five years and has built numerous custom courseware modules along the way.

Kevin has authored one book on reporting development and contributed chapters on MDX programming to a second book. He is currently developing a set of commercial video training courses for SQL Server and BI topics. He has been a SQL Server MVP since 2010 and was previously a .NET/C# MVP from 2005 to 2009.

Kevin is a frequent speaker at User Group and other community events (SQL Saturday, SharePoint Saturday, Code Camp) in the Mid-Atlantic region, and speaks occasionally at conferences. In 2012 Kevin worked with Microsoft TechNet Radio on a 13 week webcast series on new features in SQL Server 2012.

For more information, check out Kevin’s main site at http://www.KevinSGoff.net, or email him at kgoff@kevinsgoff.net

kgoff@commongroundsolutions.net