Tip 9: Using the SQL Server COALESCE Function
Suppose I have a form (or Web page) that allows users to retrieve customers based on several input criteria, such as first and last name, address, city, zip, etc. The user may enter one field or multiple fields. I need to write a stored procedure to examine all possible parameters, but only query on those that the user entered.
I could write a stored procedure that examines each parameter, constructs a SQL SELECT string based on parameters the user entered, and execute the string using dynamic SQL. Many SQL developers opt for this approach.
Alternatively I can use the SQL Server COALESCE
function as shown in the following code:
-- This will work in both SQL2000 and SQL2005
-- You can use COALESCE to query on only those search values
-- that are not NULL
DECLARE @city varchar(50), @state varchar(50), @zip varchar(50),
@FirstName varchar(50), @LastName varchar(50),
SET @FirstName = 'Kevin'
SET @State = 'NY'
SELECT * FROM CUSTOMERS WHERE
FirstName = COALESCE(@FirstName,FirstName) AND
LastName = COALESCE(@LastName,LastName) AND
Address = COALESCE(@Address,Address) AND
City = COALESCE(@City,City) AND
State = COALESCE(@State,State) AND
Zip = COALESCE(@Zip,Zip)
is available both in SQL Server 2000 and SQL Server 2005, and gives me an alternative approach that arguably leads to cleaner T-SQL code. For each search condition, I pass COALESCE
two values: the search variable, and a value to use if the search variable is NULL
. So for any search values that the user did not specify, the search defaults to the column being equal to itself. This approach is still very fast, even when querying against millions of rows.
Tip 10: Ranking in SQL 2005 within a Group
SQL Server 2005 introduced a ROW_NUMBER
function to rank result sets. I can also rank results within a group. Suppose I want to rank the top orders by customer, in descending sequence (for orders greater than $500).
shows how I can query the Northwind Orders database for orders greater than $500. I assign a ranking number for each set of customer orders. In the ROW_NUMBER OVER
statement, I can define the PARTITION
(in this context, somewhat like a group-level definition) as well as the Order
to use when ranking the result set.
Tip 11: More on T-SQL 2005: Apply, UDFs, and Table-Valued UDFs in Correlated Subqueries
ROW_NUMBER() OVER (PARTITION BY CUSTOMERID
ORDER BY (UnitPrice * Quantity) DESC)
T-SQL 2000 is a bit awkward at allowing developers to integrate table-valued UDFs into queries. Often, developers must query these UDFs into a temporary table and then use the temporary table.
|T-SQL 2005 allows developers to more efficiently integrate table-valued UDFs into queries.|
T-SQL 2005 allows developers to more efficiently integrate table-valued UDFs into queries. In Part 1 of this series, I showed an example that directly applied the results of a table-valued UDF in the same way I would use a derived table, using the new APPLY
operator. The example contained a UDF that returned the TOP N
orders from the Northwind database for a specified customer, and applied that directly against all customers in a query.
Here I'll cover another instance where I can use table-valued UDFs more directly. T-SQL 2005 allows me to use a table-valued function in a subquery, where the arguments to the UDF come from columns in the outer query.
Using the Northwind database, suppose I want to know which customers have had at least two orders for more than $5,000 (or five orders for more than a $1,000, etc.). So our first step is to build a table-valued UDF called GetCustOrders_GT_X
(see the top part of Listing 7
). The UDF contains two parameters (customer ID and dollar threshold), and returns a table variable of orders for that customer that exceed the threshold.
In the next step I run that UDF against every customer in the database, and determine which customers have at least two orders from the UDF. Ideally, I'd like to construct a subquery to pass each customer as a parameter to the UDF. Here's where the power of T-SQL 2005 comes in.
In SQL Server 2000, table-valued functions within a correlated subquery could not reference columns from the outer query. Fortunately, SQL Server 2005 removes this restriction. I can now build a subquery that uses the UDF and passes columns from the outer query as arguments to the UDF (remainder of Listing 7
Tip 12: SQL 2005 XML Enhancements
SQL Server 2000 contains a tremendous amount of functionality for dealing with XML: SQL Server 2005 extends XML with the new XML data type.
and Listing 9
demonstrate some of the capabilities in working with XML data. Listing 8
shows different ways to insert XML data into standard columns. Listing 9
demonstrates how to search for strings inside an XML column. (Special thanks to the regulars in the Microsoft SQL Server newsgroup for help with the syntax on that one!)
Tip 13: Removing a Restriction on Table Variables in SQL 2005
Microsoft introduced table variables in SQL Server 2000 to the delight of many database developers. However, table variables carried a few restrictions. One restriction was that developers could not insert the results of a stored procedure directly into a table variable. So the following code would not work in T-SQL 2000:
INSERT @tTable EXEC <sp_mystoredproc>
Fortunately, T-SQL 2005 lifts this restriction. I can now execute a stored procedure directly into a table variable (see Listing 10
Random Musings on Data
I probably sound like a broken record, but at the end of the day, much of our lives are about handling data. As I've shown in this two-part series, Visual Studio 2005 and SQL Server 2005 make our lives easier when working with data-either with major enhancements like generics and the SQL PIVOT
command, or more subtle but important changes so that I can filter on distinct rows in ADO.NET.
If you are a developer maintaining Visual Studio 2003 code in the Visual Studio 2005 environment, you may not be able to take advantage of these new data-handling capabilities right away. But you can still study the new functions as time permits, build prototypes with them, and prepare yourself for when the time comes!
Ever wish you could just lock yourself in a nice cool room for a week and research all these new capabilities? Unfortunately, the demands of the world often make that difficult. However, if you can spare a few hours, here are some great references
First, CoDe Magazine
has run some outstanding articles on T-SQL and XML in SQL Server 2005. Jim Duffy wrote an article in the January/February 2005 issue of CoDe
, titled "SQL Server 2005 T-SQL Enhancements
." Jim is an outstanding speaker and a well-rounded technology guru.
Shawn Wildermuth has an outstanding article in the May/June 2006 issue of CoDe
, titled "Making Sense of the XML DataType in SQL Server 2005
." I'd vote for this as article of the year, if such an award existed. Google Shawn's name and you'll find many tremendous online articles on ADO.NET.
Second, Manuel Abadia has written some online articles on the ObjectDataSource. He has also written his own class, called ExtendedObjectDataSource. Check out Manuel's content
Finally, the book, "Professional .NET 2.0 Generics" (Wiley Publishing) by Tod Golding is excellent and covers generics in detail. The author presents code in both C# and VB.NET.
Next Issue: Closing Thoughts
Have you ever submitted something (an article, a paper, some code, etc.) and thought of some good ideas after
the fact? Well, I'm the king of thinking of things afterwards. Fortunately, that's the type of thing that makes blogs valuable. Check my blog
for follow-up tips and notes on Baker's Dozen articles
and maybe a few additional treats!
Article Source Code
You can find the full source code
for this article on my Web site.