SQL cursor fetch problem
-
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),
-
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),
Your post is a database question, and you should post this in the database forum.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
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),
try to remove the
**next from**from the code below and try againopen curMain
--local variables
declare @dblPer int, @divisor int, @intTotMonths int,@blnSzdStatus smallintfetch 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
change it into
**fetch curMain into @LdgAcNo,....** -
try to remove the
**next from**from the code below and try againopen curMain
--local variables
declare @dblPer int, @divisor int, @intTotMonths int,@blnSzdStatus smallintfetch 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
change it into
**fetch curMain into @LdgAcNo,....** -
Be sure that the number of columns you are selecting in the select statement for the cursor is the same number of variables that you have when calling the Fetch
I have chacked it several times . Everything is fine . also the length for varchar types. the message not shown if I select into the first variable only. I don't know whats the problem with it . but I have wrote a lot of sp with such select into. :confused:
-
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),
-
I have chacked it several times . Everything is fine . also the length for varchar types. the message not shown if I select into the first variable only. I don't know whats the problem with it . but I have wrote a lot of sp with such select into. :confused:
-
Your post is a database question, and you should post this in the database forum.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
Pete O'Hanlon wrote:
you should post this in the database forum
It's a fantastic candidate for the "Coding Horrors" forum instead. :)
It is a crappy thing, but it's life -^ Carlo Pallini
-
In the query you are building you have Select Distinct and when you are creating the cursor you have another SELECT. it can be that there is the problem. when it tis being executed you are havin
SELECT SELECT DISTINCT...
No change takes place. Instead, 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 thanks for your time br, mir
-
Pete O'Hanlon wrote:
you should post this in the database forum
It's a fantastic candidate for the "Coding Horrors" forum instead. :)
It is a crappy thing, but it's life -^ Carlo Pallini
-
I have chacked it several times . Everything is fine . also the length for varchar types. the message not shown if I select into the first variable only. I don't know whats the problem with it . but I have wrote a lot of sp with such select into. :confused:
mmdullah wrote:
Everything is fine
No its not, otherwise you wouldn't be getting the error. The error TELLS you what is wrong, you need to double check it. At the end of the day, its right and you're wrong.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
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),
The problem is here:
mmdullah wrote:
declare curMain cursor for select @strsql
the select statement selects a string not a result set! no need to write all that code to generate the error, a simple script of 4/5 lines will do the trick! ;P
regards :)
-
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),
I'm not a T-SQL expert so I may be wrong here. On the other hand, if you want the opinion of database experts you should probably have posted this in the database forum. I suspect that: declare curMain cursor for select @strsql will select the literal contents of the variable strsql. It seems to me that you will need to exec the strsql variable, or whatever the T-SQL equivalent is, in order to get it to run as an actual query. Of course, if anyone from the database forum happens to be passing by and knows better than me, please feel free to correct me.
-
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),
Why are you using a cursor for this? A cursor is a very inefficient way of manipulating data, and as far as I can see, all you are doing is returning data. Why not use a stored function to return this data?
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.