SQL Server While Loop Not Returning expected Value

Question

Here the code from my function:

-- Get Country Names
DECLARE getCountriesName CURSOR FOR
    SELECT c.Name
    FROM VocabCountry c
        RIGHT OUTER JOIN ProjectCountryRelations cr
            ON cr.CountryId = c.Id
    WHERE 
        c.Id = @project_id;

-- Finally Create list to return 
OPEN getCountriesName;
FETCH NEXT FROM getCountriesName INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN  

    SET @listOfItems = @listOfItems + @Name + ', '
    FETCH NEXT FROM getCountriesName INTO @Name;

END;
CLOSE getCountriesName;
DEALLOCATE getCountriesName;

I am expecting a list of comma separated values, for example, like so:

Canada, United States of America,

I verified that the SELECT returns the countries expected.

Thanks for any help!

Eric

What output does the above code produce? I think you forgot the '@' in front of your cursor. — unhappyCrackers1
– unhappyCrackers1, Commented Jul 11, 2012 at 14:31
Do you initialize @listOfItems to anything before the loop? — Mikael Eriksson
– Mikael Eriksson, Commented Jul 11, 2012 at 14:35
"I am expecting ..." - okay, fine. But with us not having your tables and data, it's worth telling us what you're currently getting. I'm guessing NULL (as would @MikaelEriksson, I think) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 11, 2012 at 14:38

marc_s · Accepted Answer · 2012-07-11 14:34:58Z

2

If you're on SQL Server 2008 or newer, you could easily do this with a single SELECT and some FOR XML PATH magic :-)

SELECT 
    STUFF(
       (SELECT ',' + c.Name
        FROM VocabCountry c
        RIGHT OUTER JOIN ProjectCountryRelations cr
        ON cr.CountryId = c.Id
        FOR XML PATH('')
       ), 1, 1, '')

That should return a comma-separated list of those country names for you. No ugly and awful cursor needed!

answered Jul 11, 2012 at 14:34

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francis P · Accepted Answer · 2012-07-11 14:41:53Z

2

Using the FOR XML PATH will concat the values with commas.

Using the STUFF Function will remove the first comma you don't want.

So use this:

SELECT 
    STUFF(
       (SELECT ',' + c.Name
        FROM VocabCountry c
        RIGHT OUTER JOIN ProjectCountryRelations cr
        ON cr.CountryId = c.Id
        WHERE cr.Id = @project_id
        FOR XML PATH('')
       ), 1, 1, '');

See this fiddle: http://sqlfiddle.com/#!3/fd648/21

answered Jul 11, 2012 at 14:41

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

Collectives™ on Stack Overflow

SQL Server While Loop Not Returning expected Value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related