Post reply

Result Set to String

  • April 23, 2003 at 3:02 pm

    #175591

    Can someone please explain the behavior of the second and fourth SELECT in the code listed below? I don't understand how the second SELECT works, but since it does, then why doesn't the fourth? Thanks...

    Code:

    USE Northwind

    SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID DESC

    -- Why does this work?

    DECLARE @vcValue VARCHAR(255)

    SET @vcValue = 'We sell '

    SELECT @vcValue = @vcValue + CategoryName + ', ' FROM Categories ORDER BY CategoryID DESC

    PRINT @vcValue

    SELECT CategoryID INTO #temp FROM Categories WHERE CategoryID > 5

    -- Why doesn't this work?

    SET @vcValue = 'We sell '

    SELECT @vcValue = @vcValue + CategoryName + ', ' FROM Categories a, #temp b WHERE a.CategoryID = b.CategoryID ORDER BY a.CategoryID DESC

    PRINT @vcValue

    DROP TABLE #temp

    Results:

    CategoryID CategoryName

    ----------- ---------------

    8 Seafood

    7 Produce

    6 Meat/Poultry

    5 Grains/Cereals

    4 Dairy Products

    3 Confections

    2 Condiments

    1 Beverages

    (8 row(s) affected)

    We sell Seafood, Produce, Meat/Poultry, Grains/Cereals, Dairy Products, Confections, Condiments, Beverages,

    (3 row(s) affected)

    We sell Meat/Poultry,

  • April 23, 2003 at 3:32 pm

    #455104

    Oddly this works also:

    SET @vcValue = 'We sell '

    SELECT @vcValue = @vcValue + a.CategoryName + ', ' FROM Categories a WHERE a.CategoryID IN (SELECT * FROM #temp) ORDER BY a.CategoryID DESC

    PRINT @vcValue

    It appears important where SQL Server performs the Sort in the execution plan.

  • April 23, 2003 at 6:11 pm

    #455110

    Which SQL version?

    Wondering if you replace the Where with an ON clause

  • April 23, 2003 at 6:19 pm

    #455112

    SQL Server 7

  • April 23, 2003 at 10:54 pm

    #455120

    FYI

    I Just ran the query (cut and paste from the forum - no changes) on our Server (SQL2K with SP3) and the results are fine.

    (3 row(s) affected)

    We sell Seafood, Produce, Meat/Poultry,



    Greg Norris
    DBA to Development team

  • April 24, 2003 at 12:15 pm

    #455281

    I got this helpful response on another list. Hope it helps others.

    ---

    There is an interesting story behind all this. First read kb287515 @

    http://support.microsoft.com/?id=287515

    This type of query "can" be used depending on query structure and index(s) on table(s).

    To get further insight into problems read the tread: transpose problem @

    http://tinyurl.com/a84b

    Pay particular attention to replies by MS's Bill Hollinshead.

    RAC v2.1 and QALite released.

    http://www.rac4sql.net

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply