code-u-like

C#, .Net, Sql Server, Salesforce, Dynamics CRM etc,
Charity and Not-for-Profit sector

Monday, June 18, 2007

Sql 2000 Concatenation Trick

Last year I worked at a client site writing imports into the database for an Ascent CRM system. The import process was being implemented using Sql 2000 stored procedures. After getting used to a C# environment with source control and unit testing, working entirely in T-SQL was like going back to the 1970s. Still, the client had their reasons; although the job could have been done in C#, the charity in question figured that T-SQL would be cheaper to maintain in the long run, with programmers easier and cheaper to find.

So during my months of retro programming, I stumbled upon a SQL-2000 feature I hadn't seen before ... the ability to concatenate strings from multiple rows of a results set without using a cursor. It works like this:

The SET operator, as well as appearing before a SELECT statement, can also appear implicity inside a SELECT statement (and hence be used to set multple variables) like this:
declare @name varchar(40)
declare @id varchar(11)
select top 1 @name = au_lname,
  @id = au_id
from authors
print @name
print @id

So far, so normal. But it turns out you can also aggregate the SQL variable to itself, like this:
declare @combined varchar(2000)
select @combined = @combined + au_lname
from authors
where au_lname like 's%'
print @combined

The example above demonstrates the syntax, but won't actually work, due to NULLs. The variable is NULL to begin with, so every attempt to concatenate onto the end of it also returns a NULL. Here's a proper example:
declare @combined varchar(2000)
select @combined = isnull(@combined + ', ','') + au_lname
from authors
where au_lname like 's%'
print @combined

This will concatenate the last names from all the authors returned, and also put commas in between. If I'd known that before writing the first import, I could have saved myself a whole load of messing about with cursors.

(OK, some developers will point out that this sort of thing should be done in the presentation layer, but as I said, it was a 1970's style assignment - there was no presentation layer.)

If you're running SQL 2005, there are other ways of doing it, using FOR XML PATH or CROSS APPLY - see the second half of this article on aspfaq.com for details.

Labels:

1 Comments:

At January 30, 2009 at 5:07 PM , Anonymous Anonymous said...

You can also set the value on the @combinded like '' to begin with like this.

declare @combined varchar(2000)
set @combinded = ''
select @combined = @combined + au_lname
from authors
where au_lname like 's%'
print @combined

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home