T-SQL Tips

While playing around in SQL Server 2000 yesterday, I came across a couple of tips that might be useful to somebody out there in SQL land.

One frequent question on the newsgroups is how to turn a set of column values into a single string. For example, developers frequently want to take a set of email addresses, one per row, and concatenate them into a single string that can be used as a recipient list for an email sent using something like xp_sendmail.

The traditional newsgroup response is that you need a SQL cursor to do something like this. Unfortunately, the code for handling a SQL cursor is somewhat clumsy, relatively slow when compared to a set operation, and just contrary to the whole spirit of SQL. So here's some T-SQL that will do the job in a standard query.

Another frequent question is how to use T-SQL to calculate the number of business days between two dates - with business days usually being defined as non-weekend days. Traditional responses include loops or various uses of temporary tables. But here's a clever single-expression approach that does the job.