Sometimes knowing a trick or two can make your life easier. It would sure be nice if all the tricks were cataloged somewhere, or even made unnecessary somehow - but that might be another article!

What's the easiest way to return a delimited list? Maybe you want to return a list of email addresses, comma delimited so you can just send out. Or you want to use an output parameter for the return values instead of a resultset (not always a good idea). Think on that for a minute. You've got a row based, set based solution, you need to convert that to a single variable. You want to run

something like this:

Select top 50 emailaddress from MyTable

And get something like this:

email1@blah.com, email2@blah.com, email3@blah.com

Got a solution in mind? Does it involve a cursor? Subtract 5 points if it does! This may appear strange, but it works:

select @List = @List + EmailAddress + ',' from MyTable

Well, it will work if you deal with nulls correctly. Everyone remember what the behavior of nulls is in SQL 7 and later? Anything plus null equals null. That means if any value in your select is null, @List will end up being null. If you don't initialize @List to an empty string first, @List will be null! This should work:

set @List = ''

select @List = @List + EmailAddress + ',' from MyTable where EmailAddress is not

null

There are a couple warnings to keep in mind. One is that you're limited to 8000 characters (using varchar, less if you need nvarchar). That means you have to figure out if your list will exceed that limit. SQL doesn't raise an error, just truncates the string at 8000 characters. Probably the quickest way is to check for the trailing delimiter - if it's missing, you know your string got

chopped. After that, it's up to you what makes sense. You could peel off the last (partial) item, raise an error, or just go to a different plan. Another is that values could contain the delimiter itself. In the examples above I used a comma as a delimiter, picture what would happen if I used an '@' instead (example: email1@blah.com@email2@blah.com@email3@blah.com). The usual way to

handle that is to escape them somehow, commonly seen techniques are to wrap in double quotes, or to repeat the character. Regardless of the technique, your client side parser has to know how to deal with them if you implement it.

It's a trick, not a recommendation. There are times when it comes in handy.

Another "trick" I use a lot is to look for certain keywords in stored procedures to identify those that need to be upgraded/enhanced. For example, recently one of the developers on my team used the binary_checksum function in an attempt to handle a join that needed to be case sensitive. Not recommended! To be sure we fixed all instances of it's use, I ran something like this:

select distinct object_name(id) from syscomments

where text like '%binary_checksum%'

Syscomments is a system table in each database and can be queried easily as long as you haven't elected to encrypt your stored procedures. The text column contains the text of the create statement that builds the procedure itself. In the example above I include distinct because stored procedures may actually require multiple lines in syscomments, this due to the restriction that text is an nvarchar(4000) rather than a text column! Object_name is a handy function that saves you typing out the join. Without it, the query would be something like this:

select so.name from syscomments sc inner join

sysobjects so on sc.id = so.id where sc.text like '%binary_checksum%'

In this case there is almost no difference in performance between the two statements. There is no real downside to this trick, just a matter of being able to identify what you're looking for, could take a couple different searches to catch all the variations if you're looking for a more complex string/situation.