Returning Cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning Cursors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-08-16 : 09:26:53

Robert Brown writes "Dear SQLTeam

I have the following Stored Procedure:

CREATE PROCEDURE [BudgetAnalysisForReport]

AS

/* Declare local variables */

DECLARE @BudgetRevenue money
DECLARE @TotalOSCommitment money
DECLARE @TotalExpenditure money
DECLARE @TotalCredits money
DECLARE @TotalIncome money
DECLARE @TotalJournalsDebit money
DECLARE @TotalJournalsCredit money
DECLARE @TotalVirementsDebit money
DECLARE @TotalVirementsCredit money
DECLARE @BudgetID int

DECLARE budgets_cursor CURSOR FOR SELECT ID FROM Budgets;

OPEN budgets_cursor

FETCH NEXT FROM budgets_cursor
INTO @BudgetID

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @BudgetRevenue = BudgetRevenueStatusByID @BudgetID, 0
EXEC @TotalExpenditure = BudgetRevenueStatusByID @BudgetID, 1
EXEC @TotalOSCommitment = BudgetRevenueStatusByID @BudgetID, 2
EXEC @TotalCredits = BudgetRevenueStatusByID @BudgetID, 3
EXEC @TotalIncome = BudgetRevenueStatusByID @BudgetID, 4
EXEC @TotalJournalsDebit = BudgetRevenueStatusByID @BudgetID, 5
EXEC @TotalJournalsCredit = BudgetRevenueStatusByID @BudgetID, 6
EXEC @TotalVirementsDebit = BudgetRevenueStatusByID @BudgetID, 7
EXEC @TotalVirementsCredit = BudgetRevenueStatusByID @BudgetID, 8
SELECT @BudgetRevenue AS BudgetRevenue,
@TotalExpenditure as TotalExpenditure,
@TotalOSCommitment as TotalOSCommitment,
@TotalCredits as TotalCredits,
@TotalIncome as TotalIncome,
@TotalJournalsDebit as TotalJournalsDebit,
@TotalJournalsCredit as TotalJournalsCredit,
@TotalVirementsDebit as TotalVirementsDebit,
@TotalVirementsCredit as TotalVirementsCredit

FETCH NEXT FROM budgets_cursor
INTO @BudgetID
END

CLOSE budgets_cursor
DEALLOCATE budgets_cursor

The line EXEC @BudgetRevenue = BudgetRevenueStatusByID @BudgetID, 0 returns a specific type of value indicated by the final argument (from 0 to 8). When running this through the Query Analyser is does show all the values for each record from the cursor. My question is How can I return the cursor back to the client? For some reasons the records displayed are not returned. Would I have to create a temp table and insert the values into it."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources