Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Question Unanswered: Problem Adding Variables

    I am trying to wrtite a qurey that will return multiple rows as one varchar(8000). But I am howing issues when trying to add two varchar(8000) variables together. During the loop through the cursor it will just stop adding the two varchar(8000) variables together. Depending on which record that I run the query against it will stop at different point. I have run sql traces and can see that it does finish looping through the cursor completely.

    I am currently just testing this and it will be placed into a stored procedure to be call from with an application later.

    Any help is appreciated.



    declare @ticketnum as int, @mystring as varchar(8000), @myreturn as varchar(8000)

    set @ticketnum = 2
    set @myreturn = ''

    declare details cursor for
    select convert(char(25), [Date]) +
    convert(varchar(255), [Description]) +
    char(9) +
    [Login ID] +
    isnull(char(10) +
    char(9) +
    replace(convert(varchar(500), [Note]), char(10), char(10) + char(9)), '') +
    char(10)
    from [_SMDBA_].[Incident Details]
    where
    [Incident #] = @ticketnum
    order by [Sequence] asc


    open details

    FETCH NEXT FROM details
    INTO @mystring

    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @myreturn = @myreturn + @mystring
    FETCH NEXT FROM details
    INTO @mystring

    end

    CLOSE details
    DEALLOCATE details

    select @myreturn


    Example of what the query returns.
    Jul 16 2003 1:54PM Opened Call MAGIC
    I want to
    see multiple lines
    Jul 16 2003 1:54PM Call Taken By MAGIC MAGIC
    Jul 16 2003 1:54PM The Clock has been started MAGIC
    Jan 15 2004 2:00PM User Defined Statu


    What it should have returned.
    Jul 16 2003 1:54PM Opened Call MAGIC
    I want to
    see multiple lines
    Jul 16 2003 1:54PM Call Taken By MAGIC MAGIC
    Jul 16 2003 1:54PM The Clock has been started MAGIC
    Jul 16 2003 1:54PM The Clock has been started MAGIC
    Jan 15 2004 2:00PM User Defined Status Changed To CLOSED MAGIC
    Jan 15 2004 2:00PM Close Call # 1 MAGIC

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Let's see...

    You're trying to make one row out of many so you can display it as many...

    That about right?

    Doesn't matter, you're gonna blow out the 8k limit of the variable...

    and you don't need a cursor...

    USE Northwind
    GO

    DECLARE @x varchar(8000)
    SELECT @x = ''
    SELECT @x = @x + CustomerId + CHAR(10) FROM Orders
    SELECT @x
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •