T-SQL Tip of the day

Just to test the w.bloggar tool with this blog and because it's always nice to have something to say, I thought why not post a nice T-SQL Tip. (It works on Oracle too btw)

Optional parameters
When you have a table, say Orders (as in the Northwind database which comes with SQLServer), which has more than 1 foreign key (FK), it is typical that developers will query the Orders table based on a combination of these FK fields. However, as with the Orders table, this can be quite cumbersome when there are a number of FK fields. It would be nice if you could pass along any combination of these FK fields to a single stored procedure which would use these parameters to query the table in a uniform manner, so there will be no recompiles (most people who try to use optional parameters end up concatenating SQL strings in a stored procedure, which is not that good).

The idea is this: for every parameter you do not need, you pass in 'NULL' as value. For every parameter you do need, you pass in the value you want to filter on. Let's get back to the example table, the Orders table in the Northwind database. This table has 3 foreign keys: CustomerID, EmployeeID and ShipVia. If we want all Orders of a given CustomerID which are taken by a given Employee we normally wouldn't be able to use the same stored procedure which would query for all Orders for a given Customer which are shipped via a given ShipVia value. But you can! Here's how:

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
 @sCustomerID nchar(5),
 @iEmployeeID int,
 @iShipVia int
AS
SELECT  *
FROM Orders
WHERE CustomerID = COALESCE(@sCustomerID, CustomerID)
 AND
 EmployeeID = COALESCE(@iEmployeeID, EmployeeID)
 AND
 ShipVia = COALESCE(@iShipVia, ShipVia)

That's it! This stored procedure will query for Orders on any given combination of CustomerID, EmployeeID and ShipVia. If we f.e. want to select all Orders for Customer 'CHOPS' and ShipVia '1', pass these 2 values to the stored procedure and pass NULL for @iEmployeeID. This will result in the requested rows.

Caveats.
Of course there are drawbacks. One of them is that this is slower than a query which is taylored to the columns you want to filter on. It also needs a clustered index to work well, but every table should have a clustered index anyway to support fast retrievals of data.