Problem with fetching cursor into local variables

[mmdullah]

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, tblILedg

[smcnulty2000]

Just out of curiosity. If you bump this value up @strSQL varchar(2000), to something higher like @strSQL varchar(3000), do you get a different error? edit: And I don't know if this matters but you've got a disagreement here: [GLName] Varchar(200), @f_GLName varchar(50) [FileNo] Varchar(50), @f_FileNo varchar(30) Weird errors creep in on things like this down the road even if the language lets you do it. I saw one TAL issue that took months to figure out because a variable was cutting a value off with a method like this.

_____________________________ Those who study history are doomed to watch others repeat it. -Scott M.

modified on Monday, June 8, 2009 1:44 PM

[User 3229693]

select @variable will only give the value inside it you have try things like exec(@variable)