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
FROM Orders
WHERE CustomerID = COALESCE(@sCustomerID, CustomerID)
 EmployeeID = COALESCE(@iEmployeeID, EmployeeID)
 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.

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.


  • This would be a nice LLBLGen 1 addition!

  • Yeah :) I was investigating some techniques to implement variable filtersets on resultsets (i.e. joined columns from one or more tables/views), for the O/R layer for LLBLGen v2. One of them was this technique, the other was a dynamic build query using parameters and build using a stringbuilder and then fed to a command. The stored procedure as shown above was slower than the dynamicly created query. I tested it with random input parameters, and the stored procedure couldn't keep up. That was an eyeopener though, so I'll include a dynamic query engine.

    But LLBLGen v1.x can indeed win a lot with this, because the sole thing lacking in it is the ability to write custom selects on a combination of fields. With this technique that would be possible.

  • Any movent in the LLBLGen workspace?

  • Not that I'm aware of. some people joined lately but I haven't checked if they were very active. it goes as with most OSS projects: big plans by a lot of people, but a few really execute these plans.

Comments have been disabled for this content.