Stored procedure returning data from first select

[kmcmanus]

I know that I am missing something simple. I am truing to transpose some data from columns to rows. In my stored proc I am using a cursor to populate a temp table and then a final select to return the data to the report. The repor is obviously getting the column definitions from the select statement of the cursor rather than the temporary table.

Here is the Code

ALTER PROCEDURE dbo.rptCashFlow as
BEGIN
SET NOCOUNT ON
DECLARE Month_cursor CURSOR FOR
SELECT "GLCHARTDTL"."ACCOUNT", "GLCHARTDTL"."SUB_ACCOUNT", "GLAMOUNTS"."FISCAL_YEAR",
"GLAMOUNTS"."DB_BEG_BAL"+"GLAMOUNTS"."CR_BEG_BAL" 'BeginBalance',
"GLAMOUNTS"."DB_AMOUNT_01"+ "GLAMOUNTS"."CR_AMOUNT_01" 'Period01',
"GLAMOUNTS"."DB_AMOUNT_02"+ "GLAMOUNTS"."CR_AMOUNT_02" 'Period02',
"GLAMOUNTS"."DB_AMOUNT_03"+ "GLAMOUNTS"."CR_AMOUNT_03" 'Period03',
"GLAMOUNTS"."DB_AMOUNT_04"+ "GLAMOUNTS"."CR_AMOUNT_04" 'Period04',
"GLAMOUNTS"."DB_AMOUNT_05"+ "GLAMOUNTS"."CR_AMOUNT_05" 'Period05',
"GLAMOUNTS"."DB_AMOUNT_06"+ "GLAMOUNTS"."CR_AMOUNT_06" 'Period06',
"GLAMOUNTS"."DB_AMOUNT_07"+ "GLAMOUNTS"."CR_AMOUNT_07" 'Period07',
"GLAMOUNTS"."DB_AMOUNT_08"+ "GLAMOUNTS"."CR_AMOUNT_08" 'Period08',
"GLAMOUNTS"."DB_AMOUNT_09"+ "GLAMOUNTS"."CR_AMOUNT_09" 'Period09',
"GLAMOUNTS"."DB_AMOUNT_10"+ "GLAMOUNTS"."CR_AMOUNT_10" 'Period10',
"GLAMOUNTS"."DB_AMOUNT_11"+ "GLAMOUNTS"."CR_AMOUNT_11" 'Period11',
"GLAMOUNTS"."DB_AMOUNT_12"+ "GLAMOUNTS"."CR_AMOUNT_12" 'Period12'--,
--"GLAMOUNTS"."DB_AMOUNT_13"+ "GLAMOUNTS"."CR_AMOUNT_13" 'Period13'
FROM {oj ("prod"."lawuser"."GLCHARTDTL" "GLCHARTDTL"
LEFT OUTER JOIN "prod"."lawuser"."GLAMOUNTS" "GLAMOUNTS"
ON ("GLCHARTDTL"."ACCOUNT"="GLAMOUNTS"."ACCOUNT")
AND ("GLCHARTDTL"."SUB_ACCOUNT"="GLAMOUNTS"."SUB_ACCOUNT"))
INNER JOIN "prod"."lawuser"."GLNAMES" "GLNAMES"
ON ("GLAMOUNTS"."COMPANY"="GLNAMES"."COMPANY")
AND ("GLAMOUNTS"."ACCT_UNIT"="GLNAMES"."ACCT_UNIT")}
WHERE "GLCHARTDTL"."CHART_NAME"='PRIMARYCOLOR'
AND ("GLNAMES"."ACCT_UNIT">='100'
AND "GLNAMES"."ACCT_UNIT"<='499')
--AND "GLAMOUNTS"."FISCAL_YEAR" = @FiscalYear


CREATE TABLE #rptTable
(
[ACCOUNT] [int] NOT NULL ,
[SUB_ACCOUNT] [smallint] NOT NULL ,
[Period] [smallint] NOT NULL ,
[Year] [smallint] NOT NULL ,
[AMOUNT] [money] NOT NULL
)

DECLARE @BEGBAL money
DECLARE @P1BAL money
DECLARE @P2BAL money
DECLARE @P3BAL money
DECLARE @P4BAL money
DECLARE @P5BAL money
DECLARE @P6BAL money
DECLARE @P7BAL money
DECLARE @P8BAL money
DECLARE @P9BAL money
DECLARE @P10BAL money
DECLARE @P11BAL money
DECLARE @P12BAL money
DECLARE @Account INT
DECLARE @SubAccount smallINT
DECLARE @FiscalYear INT
Open Month_cursor


FETCH NEXT from Month_cursor
Into @Account, @SubAccount, @FiscalYear, @BEGBAL, @P1BAL, @P2BAL, @P3BAL, @P4BAL, @P5BAL, @P6BAL, @P7BAL, @P8BAL, @P9BAL, @P10BAL, @P11BAL, @P12BAL

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 0, @FiscalYear, @BEGBAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 1, @FiscalYear, @P1BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 2, @FiscalYear, @P2BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 3, @FiscalYear, @P3BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 4, @FiscalYear, @P4BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 5, @FiscalYear, @P5BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 6, @FiscalYear, @P6BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 7, @FiscalYear, @P7BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 8, @FiscalYear, @P8BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 9, @FiscalYear, @P9BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 10, @FiscalYear, @P10BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 11, @FiscalYear, @P11BAL)
INSERT INTO #rptTable VALUES (@Account, @SubAccount, 12, @FiscalYear, @P12BAL)

FETCH NEXT FROM Month_cursor
END

Close Month_cursor
Deallocate Month_cursor


SELECT ACCOUNT,SUB_ACCOUNT, Period,AMOUNT
FROM #rptTable

END

 

[vidru]

Change your second FETCH NEXT statement from:
[tt]
FETCH NEXT FROM Month_cursor
[/tt]
to
[tt]
FETCH NEXT from Month_cursor
Into @Account, @SubAccount, @FiscalYear, @BEGBAL, @P1BAL, @P2BAL, @P3BAL, @P4BAL, @P5BAL, @P6BAL, @P7BAL, @P8BAL, @P9BAL, @P10BAL, @P11BAL, @P12BAL
[/tt]
-dave