Post reply

How does this work?

  • March 12, 2007 at 11:23 pm

    #171625

    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

    #694598

    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

    #694601

    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

    #694618

    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

    hha@abc.com;

    hhb@abc.com;

    ...

    hhzz@abc.com;

    Interesting and potentially quite useful.

    S

     

     

  • March 13, 2007 at 2:10 pm

    #694821

    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

    #694998

    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

    #695429

    how about SQL 2005 XML way

    select stuff( ( select ','+ name from sys.tables t1 for xml path('') ),1,1,'')


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

  • March 22, 2007 at 3:50 pm

    #696496

    good one, haven't seen that before. 

    ---------------------------------------
    elsasoft.org

  • March 26, 2007 at 8:54 am

    #696899

    Hi Amit. Is it possible to do the same for grouping ?

  • March 27, 2007 at 12:12 am

    #697054

    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