March 12, 2007 at 11:23 pm
Hi People,
Someone recently pointed out to me that the following code can be used in some circumstances to avoid the use of a cursor (although I guess only if you know for sure in advance that it won't exceed 8000 chars...).
declare @recipients varchar(8000)
set @recipients = ''
select @recipients = @recipients + isnull(email,'') + ';' from staff
select @recipients
I have never seen anything like it before and was wanting an official reference to this usage - partly because I'm interested in how its implemented under the hood so to speak and partly to work out the full scope functionality of this and whether it might be useful to me under other circumstances.
Cheers,
March 13, 2007 at 2:30 am
hi,
ur query is something like selecting all records from staff table which returns multiple records.
in the above query, u r only taking email column from table in a single column. the only difference is that u r replacing null value in email field with blank value & then appending ;
so it will return
abc@test.com; ;gef@tt.com;
& so on
regards
Anurag
March 13, 2007 at 2:41 am
Hi - yes I know what its doing - I'm trying to understand why its doing it - I've never seen something like this before which automatically produces a single row and I can't find any reference to it in the SQL reference. Normally I have to use a cursor for such a function. Can anyone point me to where this behaviour is defined? Thanks.
March 13, 2007 at 3:52 am
I was surprised by that, but I think it is doing this:
@recipients = ''
@recipients = 'a@abc.com;'
@recipients = 'a@abc.com;b@abc.com;'
@recipients = 'a@abc.com;b@abc.com;.....zz@abc.com'
so essentially it is looping through staff appending the next value of email to the list. Because @recipients is on both sides of the '=' it gets aggregated - if the select statement was
select @abc = @recipients + isnull(email,'') + ';' from staff
(and had set @recipients to, for example 'hh'
then you would return the following list
...
Interesting and potentially quite useful.
S
March 13, 2007 at 2:10 pm
Yes very interesting which is why I'm very keen to see a reference to it somewhere because I'd love to know what is happening under the hood... e.g. is it actually using a cursor anyway? Or is it using a better mechanism than that? I'd very much like to see an official reference to this behaviour if anyone knows of one.
March 14, 2007 at 9:53 am
Hi,
I have used a similar looping system a few times, and have noticed that it has to do with the way you set your declared variable.
You have told the server to perform a select from a table and to give the returned value to the variable. The select gives more than one result back, so the system automatically gives the value back to the variable until char(8000) is full or the table has been read through.
A very helpful website talking about looping in t-sql without using the evil of evils (cursors) is here:
http://www.extremeexperts.com/SQL/Articles/IterateTSQLResult.aspx
The second example on that webpage has been extremely helpful for me and works on a similar system to the one you mentioned here.
HTH
Regards,
WilliamD
March 16, 2007 at 6:04 am
how about SQL 2005 XML way
select stuff( ( select ','+ name from sys.tables t1 for xml path('') ),1,1,'')
March 22, 2007 at 3:50 pm
good one, haven't seen that before.
---------------------------------------
elsasoft.org
March 26, 2007 at 8:54 am
Hi Amit. Is it possible to do the same for grouping ?
March 27, 2007 at 12:12 am
I've always prefered the more concise:
DECLARE @recipients varchar(8000) SELECT @recipients = COALESCE(@recipients + ';','') + COALESCE(email, '') FROM staff SELECT @recipients
Using this code, you don't have a trailing semicolon and you don't need to initialize your variable.
This code effectively works the same way. For the first record, @recipients is null and since null + ; evaluates to NULL, the COALESCE returns an empty string (coalesce is basically an ANSI ISNULL that can accept more than two values).
For each additional record, COALESCE returns your variable with a semicolon appended and your query appends the next email address.
Just out of interest, do you really want to return null and empty email addresses?
SQL guy and Houston Magician
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply