Poor Man's Recursion

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Christian Gustafson (cgustafson_at_gmail.com)
Date: 09/24/04 

Date: 24 Sep 2004 09:15:05 -0700

We use a lot of comma-delimited lists of integers to represent sets of
rows and IDs in SQL Server, since we can't use arrays, etc. for this.
The problem is that it can be a pain to select a set of these values
from several rows and then concatenate them into a single value.

I've found an elegant way to do this, which I call "Poor Man's
Recursion", but I'm not quite sure how and why this works. Can
someone from the MS SQL team confirm that this is the correct behavior
for the query engine, and that this won't disappear in future
releases? Check it out.

Example using pubsdb: 

--
use pubs
declare @jobList as varchar(4000)
select @jobList = isnull(@jobList + ',' + cast(j.job_id as
varchar(12)), cast(j.job_id as varchar(12)))
from (
   select distinct job_id, job_desc
   from jobs
) as j
order by j.job_desc
 
select @joblist as 'JobIDs'
JobIDs
--------------------------------
9,3,2,4,14,12,6,7,1,11,10,8,5,13
(1 row(s) affected)
A very powerful technique, IMO, if you have ever had to use a cursor
to do this.  Now you have a comma-delimited list of IDs that we can
use as a set and pass into other sprocs, table-based UDFs, trees, etc.
Please let me know if this is Kosher T-SQL or an unexpected result.
thanks!
Christian Gustafson
Seattle, WA

Relevant Pages

  • RE: Poor Mans Recursion
    ... > rows and IDs in SQL Server, since we can't use arrays, etc. for this. ... Why do you feel the need to represent arrays in SQL? ... Presentation belongs client-side rather than in the database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Filtering DB Call based on local Data
    ... Send the Ids as XML, parse it in a SP into a temp table then Join and return ... SQL Server will take care of the rest. ... single parameter to your query (it can be a stored procedure or dynamic ... specific to the order from the order table on a Sql Server Database ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: XPS to IDS - a positive note for all from the road
    ... purchasable optional extras in a version, Enterprise, where - no two ways about this - Informix is *already* more expensive than Oracle and DB2 even before you buy the optional extras! ... Now that is something that IBM could easily do something about, even just to put IDS on the same footing price-wise as DB2 ... ... SQL Server EE: $25,000 ...
    (comp.databases.informix)
  • Re: 11.50.xC4 Compression with workgroup edition?
    ... And don't forget that compression is free for with SQL Server 2008 ... Enterprise edition. ... Why is it at the very least not free for IDS ... On the DB2 side compression sells like ice cream at the beach. ...
    (comp.databases.informix)
  • Re: JDBC and SE5
    ... you need the Inet connectivity as far as I know ... Subject: SQL Server and IDS ... You could substitute the openquery with a four part naming ...
    (comp.databases.informix)