Problem with fetching cursor into local variables

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 2005 Forums

Transact-SQL (2005)

Problem with fetching cursor into local variables

Author

Topic

mmdullah
Starting Member

10 Posts

Posted - 2009-06-08 : 06:02:48

Dear experts,
problem arises when fetching cursors.
please help asap.

the function code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION Get_Coll_Ovd_Report
(
@fromDate datetime,
@ToDate datetime,
@dtOvdDate datetime,
@dtOvdDatePrev datetime,
@strAndPaid VARCHAR(300),
@rdoFileInfoSpecific int,
@SpecificFileNo varchar(20),
@strAndFOfficer varchar(100),
@strAndZone varchar(100),
@strCollType varchar(50),
@rdoStatusNID smallint,
@rdoStatusBankOpen smallint,
@chkOvdVal smallint,
@rdoRepTypeAllDet smallint
)Returns @retVal table(
[SLNo] int,
[TDate] DateTime,
[GLRef] Varchar(50),
[LdgAcNo] Varchar(50),
[AcName] Varchar(200),
[MRNo] int,
[GLName] Varchar(200),
[Cash] decimal(18,6),
[Clear] decimal(18,6),
[Trans] decimal(18,6),
[CnTot] decimal(18,6),
[FileNo] Varchar(50),
[NidStat] smallint,
[UserID] Varchar(50),
[District] Varchar(50),
[PaidAt] Varchar(50),
[ZMrNo] Varchar(50),
[ZMrDate] DateTime,
[Model] Varchar(50),
[FOID] int,
[FClBal] decimal(18,6),
[TotCol] decimal(18,6),
[BLoan] decimal(18,6),
[BTotDr] decimal(18,6),
[OvdPrv] decimal(18,6),
[P1] decimal(18,6),
[P2] decimal(18,6))
AS
BEGIN
declare @f_SLNo int,@f_TDate datetime,@f_GLRef varchar(50), @f_LdgAcNo varchar(50) , @f_AcName varchar(200) , @f_MRNo int, @f_GLName varchar(50), @f_Cash decimal(18,6), @f_Clear decimal(18,6), @f_Trans decimal(18,6), @f_CnTot decimal(18,6), @f_FileNo varchar(30) , @f_NidStat smallint , @f_UserID varchar(50), @f_District varchar(50) , @f_PaidAt Varchar(50), @f_ZMrNo Varchar(50), @f_ZMrDate datetime, @f_Model Varchar(50) , @f_FOID int, @f_FClBal decimal(18,6), @f_TotCol decimal(18,6), @f_BLoan decimal(18,6), @f_BTotDr decimal(18,6), @f_OvdPrv decimal(18,6), @f_P1 decimal(18,6), @f_P2 decimal(18,6)

declare @strSQL varchar(2000),
@LdgAcNo varchar(12),
@MAcName varchar(80),
@FileNo varchar(30),
@MaxiInstlAmt decimal(18,0),
@MaxiMinInstlAmt decimal(18,0),
@DCPDt datetime,
@FClBal decimal(18,6),
@Ac_Status smallint,
@FileClosedDt datetime,
@ReconV varchar(15),
@GuestPrdComp smallint,
@TermComp smallint,
@FOID int,
@FOName Varchar(100),
@DistName Varchar(50),
@ZName Varchar(50),
@Model Varchar(150),
@Ac_StatusB smallint,
@BClosedDt datetime,
@SzdStatus varchar(50),
@SzdDate datetime,
@IntRt decimal(18,6),
@LoanAmt decimal(18,6),
@TotAmt decimal(18,6)

set @strSQL = 'SELECT Distinct
tblILedger.LdgAcNo,
tblBuyerMst.MAcName,
tblVLedger.FileNo,
tblVLedger.MaxiInstlAmt,
tblVLedger.MaxiMinInstlAmt,
tblDCDetail.DCPDt,
tblILedger.FClBal,
tblILedger.Ac_Status,
tblVLedger.FileClosedDt,
tblVLedger.ReconV,
tblVLedger.GuestPrdComp,
tblVLedger.TermComp,
tblVLedger.FOID,
tblFOfficer.FOName,
tblDistMst.DistName,
tblZoneMst.ZName,
tblModelMst.Model,
tblLoanDoc.Ac_StatusB,
tblLoanDoc.BClosedDt,
tblVLedger.SzdStatus,
tblVLedger.SzdDate,
tblVLedger.IntRt,
tblLoanDoc.LoanAmt,
ISNULL((SELECT SUM(tblMRMst.TotAmt)From tblMRMst Where (tblMRMst.LdgAcNo = tblILedger.LdgAcNo) AND (tblMRMst.TotAmt > 0) AND (tblMRMst.Rvrs = 0 OR tblMRMst.Rvrs is Null) ' + @strAndPaid + '),0) AS TotAmt '+

'FROM tblVLedger INNER JOIN tblILedger ON tblILedger.LdgAcNo = tblVLedger.LdgAcNo INNER JOIN
tblDCDetail ON tblVLedger.DCNo = tblDCDetail.DCNo INNER JOIN tblBuyerMst ON
tblDCDetail.BNo = tblBuyerMst.BNo INNER JOIN
tblDistMst ON tblBuyerMst.MDistID = tblDistMst.DistID INNER JOIN tblZoneMst ON tblBuyerMst.MZID = tblZoneMst.ZID
Inner Join tblModelMst ON tblDCDetail.MNo=tblModelMst.MNo
Inner Join tblFOfficer ON tblVLedger.FOID=tblFOfficer.FOID
INNER JOIN tblLoanDoc ON tblDCDetail.DCNo = tblLoanDoc.DCNo
WHERE (tblDCDetail.DCPDt <'''+cast(@fromDate as varchar(20))+''')
AND ((tblILedger.Ac_status = 1) OR ((tblILedger.Ac_status = 0) AND (tblVLedger.FileClosedDt >='''+cast(@fromDate as varchar(20))+''')))'

if (@rdoFileInfoSpecific=1)
begin
set @strSQL = @strSQL + ' AND (tblVLedger.FileNo= '+@SpecificFileNo+')'
end
if (@rdoStatusBankOpen=1)
begin
set @strSQL = @strSQL + ' AND (tblLoanDoc.Ac_StatusB=1 OR (tblLoanDoc.Ac_StatusB = 0 AND tblLoanDoc.BClosedDt >= '''+cast(@fromDate as varchar(20))+'''))'
end
else
begin
if(@rdoStatusNID=1)
begin
set @strSQL = @strSQL + ' AND (tblLoanDoc.Ac_StatusB=0 AND (tblLoanDoc.BClosedDt is null OR tblLoanDoc.BClosedDt< '''+cast(@fromDate as varchar(20))+'''))'
end
end
set @strSQL = @strSQL + @strAndFOfficer;
set @strSQL = @strSQL + @strAndZone;
set @strSQL = @strSQL + @strCollType;
set @strSQL = @strSQL + ' ORDER BY tblZoneMst.ZName,tblVLedger.FOID,tblVLedger.FileNo'

-- end of main query string

declare curMain cursor for select @strsql

open curMain
--local variables
declare @dblPer int, @divisor int, @intTotMonths int,@blnSzdStatus smallint

fetch next from curMain into @LdgAcNo , @MAcName , @FileNo , @MaxiInstlAmt , @MaxiMinInstlAmt , @DCPDt , @FClBal, @Ac_Status , @FileClosedDt , @ReconV , @GuestPrdComp , @TermComp , @FOID , @FOName , @DistName , @ZName , @Model , @Ac_StatusB , @BClosedDt , @SzdStatus , @SzdDate , @IntRt , @LoanAmt , @TotAmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- A lot of codes goes here . but they do not raises the error. I checked it.
--insert into @retVal values(@f_SLNo,@f_TDate,@f_GLRef , @f_LdgAcNo , @f_AcName , @f_MRNo, @f_GLName , @f_Cash, @f_Clear, @f_Trans, @f_CnTot, @f_FileNo , @f_NidStat , @f_UserID , @f_District , @f_PaidAt , @f_ZMrNo , @f_ZMrDate, @f_Model , @f_FOID, @f_FClBal, @f_TotCol, @f_BLoan, @f_BTotDr, @f_OvdPrv, @f_P1, @f_P2)
fetch next from curMain into @LdgAcNo , @MAcName , @FileNo , @MaxiInstlAmt , @MaxiMinInstlAmt , @DCPDt , @FClBal, @Ac_Status , @FileClosedDt , @ReconV , @GuestPrdComp , @TermComp , @FOID , @FOName , @DistName , @ZName , @Model , @Ac_StatusB , @BClosedDt , @SzdStatus , @SzdDate , @IntRt , @LoanAmt , @TotAmt
END
CLOSE curMain
DEALLOCATE curMain
return
END

calling with:
select * from dbo.Get_Coll_Ovd_Report
(
'14 Apr 2009',
'10 May 2009',
'13 Apr 2009',
'10 May 2009',
'',--' this will be andpaid clause',
0,-- this is tag for file specific
'900',-- this is file no
'470',--'officer id', -- all officer
'',--' zone id',-- zone id
'',--'coll type',
0,--is nid
0,
0,
1
-- is bank open
)

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

please help in asap.

thanks for your time
mir

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-08 : 06:57:37

Make a print @strsql and see if the columns in your select map to your variables of your INTO list

No, you're never too old to Yak'n'Roll if you're too young to die.

mmdullah
Starting Member

10 Posts

Posted - 2009-06-08 : 07:15:59

thanks for your reply.
I have tested it several times. no column mismatching.
But would you please tell me the ways to open cursors excluding
Open cursor cursor_NAme for select_statement
I think as I provide the select_statement as a string variable , it may functioning another way

br,
Mir

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-09 : 05:35:28

Cursors don't work that way.

Your cursor is only brining back 1 value (which is the sql text you put into the @strSQL variable).

You can't use dynamic sql in functions at all.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources