?
Solved

Help with Cursor error??

Posted on 2008-11-03
4
Medium Priority
?
273 Views
Last Modified: 2012-06-27
I'm modifying a stored procedure to add a variable called @pending_FA which will display a ** in front of the description tag in the XML file that is created with this procedure.  I added an IF statement in the second cursor looking for thiis value.  I'm getting the error:

Server: Msg 16924, Level 16, State 1, Procedure CUS_sp_CASHNetEBillXml_test, Line 158
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

Not very experienced using cursors, and know they are inefficient, but I can't change that part of the coce.  Any help is appreciated
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
 
 
 
 
 
ALTER      PROCEDURE [DBO].[CUS_sp_CASHNetEBillXml_test]
	@SUBSID_GRP_CDE char(2),
	@FileDrive varchar(20) = 'C:',
	@AddressCode char(4) = '*LHP'
AS
 
--Define Variables
DECLARE
	@FileLocation varchar(1000),
	@FileLocXml varchar(1000),
	@CASHNetTextRow varchar(1000),
	@FS int,
	@FileLocBalances varchar(1000),
	@FileLocToCASHNet varchar(1000),
	@FileLocFileRename varchar(1000),
	@OLE int,
	@FileID int,
	@Student_Name varchar(60),
	@Student_ID varchar(30),
	@Payment_Amount varchar(20),
	@Payment_Duedate varchar(10),
	@Statement_Date varchar(10),
	@Student_Address1 varchar(60),
	@Student_Address2 varchar(60),
	@Student_Address3 varchar(60),
	@Student_City char(25),
	@Student_State char(2),
	@Student_Zip char(12),
	@Beginning_Balance varchar(20),
	@Statement_Balance varchar(20),
	@Date varchar(10),
	@Description varchar(40),
	@Amount numeric(11, 2),
	@RunningTotal numeric(11, 2),
	@Pending_FA varchar(2)
 
 
--Set Variable's values
SET @FileLocation = RTRIM(@FileDrive) + '\CASHNetFiles\To\'
SET @Statement_Date = CONVERT(VARCHAR(10), GetDate(), 101)
SET @FileLocXml = @FileLocation + 'CASHNetEBillXml.txt'
 
EXECUTE @OLE = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLE <> 0 PRINT 'Scripting.FileSystemObject'
 
--Open a file
execute @OLE = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileLocXml, 2, 1
IF @OLE <> 0 PRINT 'OpenTextFile'
 
--CREATE eBill XML DATA
Declare eBillCursor Scroll Cursor For
 
SELECT	NFV.FIRST_MIDDLE_LAST Student_Name,
		CONVERT(VARCHAR(30), NFV.ID_NUM) Student_ID,
		CONVERT(VARCHAR(20), dbo.CUS_udf_GetStatementBalance(NFV.ID_NUM, @SUBSID_GRP_CDE, 0)) Payment_Amount,
		CONVERT(VARCHAR(10), SC.PERIOD_END_DTE, 101) Payment_Duedate,
		CONVERT(VARCHAR(10), SC.STMT_DUE_DTE, 101) Statement_Date,
		ISNULL(AM.ADDR_LINE_1, '') Student_Address1,
		ISNULL(AM.ADDR_LINE_2, '') Student_Address2,
		ISNULL(AM.ADDR_LINE_3, '') Student_Address3,
		ISNULL(AM.CITY, '') Student_City,
		ISNULL(AM.STATE, '') Student_State,
		ISNULL(AM.ZIP, '') Student_Zip,
		CONVERT(VARCHAR(20), dbo.CUS_udf_GetBeginningBalance(NFV.ID_NUM, @SUBSID_GRP_CDE)) Beginning_Balance,
		CONVERT(VARCHAR(20), dbo.CUS_udf_GetStatementBalance(NFV.ID_NUM, @SUBSID_GRP_CDE, 1)) Statement_Balance
FROM	NAME_FORMAT_VIEW NFV JOIN SUBSID_CTL SC ON
		SC.GENKEY = '1'
		JOIN STUDENT_MASTER SM
		ON NFV.ID_NUM = SM.ID_NUM
		JOIN REG_CONFIG RC
		ON SM.MOST_RECNT_YR_ENR = RC.CUR_YR_DFLT AND
		SM.MOST_RECNT_TRM_ENR = RC.CUR_TRM_DFLT
		LEFT OUTER JOIN ADDRESS_MASTER AM
		ON NFV.ID_NUM = AM.ID_NUM AND
		AM.ADDR_CDE = @AddressCode
 
--Beginning tag
SET @CASHNetTextRow = '<Billings>'
execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @CASHNetTextRow
IF @OLE <> 0 PRINT 'WriteLine'
 
OPEN eBillCursor
FETCH NEXT FROM eBillCursor INTO
	@Student_Name, @Student_ID, @Payment_Amount, @Payment_Duedate, @Statement_Date,
	@Student_Address1, @Student_Address2, @Student_Address3,
	@Student_City, @Student_State, @Student_Zip, @Beginning_Balance, @Statement_Balance
While @@Fetch_Status = 0
Begin
	SET @RunningTotal = 0
	SET @CASHNetTextRow = '<Billing>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_Name>' + @Student_Name + '</Student_Name>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_ID>' + @Student_ID + '</Student_ID>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Payment_Amount>' + @Payment_Amount + '</Payment_Amount>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Payment_Duedate>' + @Payment_Duedate + '</Payment_Duedate>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Statement_Date>' + @Statement_Date + '</Statement_Date>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_Address1>' + @Student_Address1 + '</Student_Address1>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_Address2>' + @Student_Address2 + '</Student_Address2>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_Address3>' + @Student_Address3 + '</Student_Address3>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_City>' + @Student_City + '</Student_City>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_State>' + @Student_State + '</Student_State>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Student_Zip>' + @Student_Zip + '</Student_Zip>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Beginning_Balance>' + @Beginning_Balance + '</Beginning_Balance>'
	SET @CASHNetTextRow = @CASHNetTextRow + '<Statement_Balance>' + @Statement_Balance + '</Statement_Balance>'
	execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @CASHNetTextRow
	IF @OLE <> 0 PRINT 'WriteLine'
 
	Declare eBillCursorDetail Scroll Cursor For
	SELECT	CONVERT(VARCHAR(10), TH.TRANS_DTE, 101) Date,
			TH.SOURCE_CDE Source,
			TH.TRANS_DESC Description,
			TH.TRANS_AMT Amount
	FROM	SUBSID_MASTER SM JOIN SUBSID_GRP SG
			ON SM.SUBSID_CDE = SG.SUBSID_CDE 
			AND SM.ID_NUM = CONVERT(INT, @Student_ID)
			AND SG.SUBSID_GRP_CDE = @SUBSID_GRP_CDE
			JOIN TRANS_HIST TH ON
			SM.SUBSID_CDE = TH.SUBSID_CDE AND
			SM.ID_NUM = TH.ID_NUM AND
			TH.SUBSID_TRANS_STS IN ('C', 'Y', 'U', 'S')
	ORDER BY
			TH.TRANS_DTE
 
	OPEN eBillCursorDetail
	FETCH NEXT FROM eBillCursorDetail INTO @Date, @Pending_FA, @Description, @Amount
	While @@Fetch_Status = 0
	Begin
		SET @RunningTotal = @RunningTotal + @Amount
		SET @CASHNetTextRow = '<Activity>'
		SET @CASHNetTextRow = @CASHNetTextRow + '<Date>' + @Date + '</Date>'
		IF @Pending_FA = '@F'
		BEGIN
			SET @CASHNetTextRow = @CASHNetTextRow + '<Description>' + '** ' + @Description +'</Description>'
		END
		ELSE
		BEGIN	
			SET @CASHNetTextRow = @CASHNetTextRow + '<Description>' + @Description + '</Description>'
		END
		IF @Amount < 0
		BEGIN
			SET @CASHNetTextRow = @CASHNetTextRow + '<Charges>0.00</Charges>'
			SET @CASHNetTextRow = @CASHNetTextRow + '<Credits>' + CONVERT(varchar(20), @Amount) + '</Credits>'
		END
		ELSE
		BEGIN
			SET @CASHNetTextRow = @CASHNetTextRow + '<Charges>' + CONVERT(varchar(20), @Amount) + '</Charges>'
			SET @CASHNetTextRow = @CASHNetTextRow + '<Credits>0.00</Credits>'
		END
		SET @CASHNetTextRow = @CASHNetTextRow + '<Total>' + CONVERT(varchar(20), @RunningTotal) + '</Total>'
		SET @CASHNetTextRow = @CASHNetTextRow + '</Activity>'
		execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @CASHNetTextRow
		IF @OLE <> 0 PRINT 'WriteLine'
 
		FETCH NEXT FROM eBillCursorDetail INTO @Date, @Description, @Amount
	End
	Close eBillCursorDetail
	Deallocate eBillCursorDetail
 
	SET @CASHNetTextRow = '</Billing>'
	execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @CASHNetTextRow
	IF @OLE <> 0 PRINT 'WriteLine'
 
	FETCH NEXT FROM eBillCursor INTO
		@Student_Name, @Student_ID, @Payment_Amount, @Payment_Duedate, @Statement_Date,
		@Student_Address1, @Student_Address2, @Student_Address3,
		@Student_City, @Student_State, @Student_Zip, @Beginning_Balance, @Statement_Balance
End
Close eBillCursor
Deallocate eBillCursor
 
SET @CASHNetTextRow = '</Billings>'
execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @CASHNetTextRow
IF @OLE <> 0 PRINT 'WriteLine'
 
EXECUTE @OLE = sp_OADestroy @FileID

Open in new window

0
Comment
Question by:jasonbrandt3
  • 2
4 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 22872249
Just a guess but I'd consider putting either "[" and "]" (or renaming) your aliases as you have used the reserved words Date and Description

SELECT      CONVERT(VARCHAR(10), TH.TRANS_DTE, 101) [Date],
                  TH.SOURCE_CDE Source,
                  TH.TRANS_DESC [Description],
                  TH.TRANS_AMT Amount

0
 
LVL 3

Expert Comment

by:LeskosekDenis
ID: 22875019
You can also completely remove aliases because you wouldn't need them in cursor anyway.

Also move code after OPEN eBillCursor
--Beginning tag
SET @CASHNetTextRow = '<Billings>'
execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @CASHNetTextRow
IF @OLE <> 0 PRINT 'WriteLine'
0
 
LVL 3

Accepted Solution

by:
LeskosekDenis earned 2000 total points
ID: 22875044
Also add parameter @Pending_FA to line 163 of your code snippet
0
 

Author Closing Comment

by:jasonbrandt3
ID: 31512898
I added the brackets and the @Pending_FA to line 163 and this corrected the problem.  The funny thing is a large third party company wrote this stored procedure and couldn't figure out how add this variable!  I appreciate the help!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question