Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: How do i return a multiple resultset in MSSQL2000?

    I can not get a multiple row resultset to display or even get sent to my client application running on coldfusion.
    What is the problem with the code?
    How do i display and return a resultset to my coldfusion client application?
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO




    ALTER PROCEDURE dbo.nTransaction
    (@pAccountNo varchar(30), @N int, @ntransCursor CURSOR VARYING OUTPUT, @nValue varchar(4000) OUTPUT)
    AS

    set @N = 0

    SET ROWCOUNT @N

    SET @ntransCursor = CURSOR FOR
    -- FORWARD_ONLY STATIC
    SELECT CONVERT(varchar,Eh.EntryID), Eh.EntryReference,E.AccountNo, E.Narrative, E.Amount, Eh.EntryDate
    FROM entryheaders as Eh cross join entrys as E
    WHERE Eh.EntrySerial = E.EntrySerial and AccountNo = @pAccountNo
    ORDER BY Eh.EntryID DESC
    SET ROWCOUNT 0

    OPEN @ntransCursor

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    FETCH NEXT FROM @ntransCursor into @nValue

    END
    CLOSE @ntransCursor
    DEALLOCATE @ntransCursor;





    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    Pls i need urgent help!!!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think you've been sniffing too much Oracle! Try the SQL Server approach, something like:
    Code:
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    ALTER PROCEDURE dbo.nTransaction
    (@pAccountNo varchar(30)) 
    AS
    
    SELECT CONVERT(varchar,Eh.EntryID)
    ,  Eh.EntryReference, E.AccountNo, E.Narrative
    ,  E.Amount, Eh.EntryDate
       FROM entryheaders as Eh
       JOIN entrys as E 
          ON Eh.EntrySerial = E.EntrySerial
       WHERE  AccountNo = @pAccountNo
       ORDER BY Eh.EntryID DESC 
    
    RETURN
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    -PatP

  3. #3
    Join Date
    Apr 2006
    Posts
    16

    Thank u

    Thank u. I got it using normal select statement in my Stored Procedure.

    God bless u.

Posting Permissions

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