December 28, 2011 at 6:59 am
hello all.
I use one cursor that use one dynamic temprary table,this tables alters and add columns.how to declare cursor that this cursor can complete?
this is my code:
create procedure [dbo].[Pay_Phd_PayrollReport2]
(
@Where nvarchar(max)
,@WherePay nvarchar(max)
,@Sort nvarchar(max)
)
as
begin
declare @sql nvarchar(max),
@Pfname nvarchar(50),
@Pfvalue int,
@Pfprimeryvalue int,
@Pfsecondaryvalue int,
@Pfoutvalue int,
@PfSumPayedValue bigint,
@PAY_PhbOutFinal bigint,
@KPfname nvarchar(50),
@KPfvalue int,
@KPfprimeryvalue int,
@KPfsecondaryvalue int,
@KPfoutvalue int,
@KPfSumPayedValue bigint,
@KarfarmaPfname nvarchar(50),
@KarfarmaPfvalue int,
@KarfarmaPfprimeryvalue int,
@KarfarmaPfsecondaryvalue int,
@KarfarmaPfoutvalue int,
@KarfarmaPfSumPayedValue bigint,
@Khales bigint,
@Nakhales bigint,
@Kosoorat bigint,
@Karkerd bigint,
@SumFinalOut bigint,
@SumFinalOutKosoorat bigint,
@PersonID bigint,
@PayedMonthId int,
@MonthId int,
@Pay_phbNote nvarchar(200),
@i int
create table #Out (ID int IDENTITY(1,1),TBL_PersonnelID bigint,Pay_PayedMonthID_fk int,Pay_MonthID_fk int,TBL_PersonnelFirstName nvarchar(250),TBL_PersonnelLastName nvarchar(250),Pay_MonthName nvarchar(100),Pay_PayedMonthName nvarchar(100),PAY_phbCoefficient nvarchar(10),pay_PhbNote nvarchar(200),
Khales bigint,Nakhales bigint,Kosoorat bigint,Karkerd bigint,
CONSTRAINT [PK_#Out] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
set @sql='select distinct TBL_PersonnelID,Pay_PayedMonthID_fk,Pay_MonthID_fk,TBL_PersonnelFirstName,TBL_PersonnelLastName,Pay_MonthName,Pay_PayedMonthName ,cast(PAY_phbCoefficient as nvarchar(10)) as PAY_phbCoefficient,pay_PhbNote,
0,0,0,0
from V_Pay_HistoryBack where '+@Where+'order by '+@Sort+',pay_monthid_fk,pay_PhbNote,cast(PAY_phbCoefficient as nvarchar(10))'
insert into #Out exec sp_executesql @sql
DECLARE Cursor_Primary CURSOR DYNAMIC for
select TBL_PersonnelID,Pay_PayedMonthID_fk,Pay_MonthID_fk,pay_PhbNote from #Out
OPEN Cursor_Primary
FETCH NEXT FROM Cursor_Primary
INTO @PersonID,@PayedMonthId,@MonthId,@Pay_phbNote
WHILE @@FETCH_STATUS = 0
BEGIN
set @SumFinalOut=0
create table #Pay(PAY_PfName nvarchar(70),PfNameAmount nvarchar(200),PAY_PfID_fk int,PAY_PhbAmount int,PAY_PhbOutFinal int,PAY_PhbPrimeryValue int,PAY_PhbSecondaryValue int,PAY_PhbOutValue int,SumPayedValue bigint)
set @sql='select PAY_PfName,PAY_PfName+'' ''+cast(PAY_PhbAmount as nvarchar(200)) as PfNameAmount, PAY_PfID_fk,PAY_PhbAmount,PAY_PhbOutFinal,PAY_PhbPrimeryValue,PAY_PhbSecondaryValue,PAY_PhbOutValue,isnull(PAY_PhbPrimeryValue,0)+isnull(PAY_PhbSecondaryValue,0) as SumPayedValue
from V_Pay_HistoryBack where '+@WherePay+' and TBL_PersonnelID_fk='+cast(@PersonID as nvarchar(50))+' and Pay_Monthid_fk='+cast(@MonthId as nvarchar(50))+' and PAY_PayedMonthID_fk='+cast(@PayedMonthId as nvarchar(50))+' and PAY_phbnote ='''+@Pay_phbNote+'''order by '+@Sort
insert into #Pay exec sp_executesql @sql
set @i=0
DECLARE Cursor_Pay CURSOR FOR
select PfNameAmount,PAY_PhbOutFinal,PAY_PhbPrimeryValue,PAY_PhbSecondaryValue,PAY_PhbOutValue,SumPayedValue,PAY_PhbOutFinal from #Pay
OPEN Cursor_Pay
FETCH NEXT FROM Cursor_Pay
INTO @Pfname,@Pfvalue,@Pfprimeryvalue,@Pfsecondaryvalue,@Pfoutvalue,@PfSumPayedValue,@PAY_PhbOutFinal
WHILE @@FETCH_STATUS =0
BEGIN
set @SumFinalOut=@SumFinalOut+@PAY_PhbOutFinal
set @i=@i+1
set @sql='alter table #Out add[Pfname'+cast(@i as nvarchar(2))+'] nvarchar(70),
[Pfvalue'+cast(@i as nvarchar(2))+'] int,
[Pfprimeryvalue'+cast(@i as nvarchar(2))+'] int,
[Pfsecondaryvalue'+cast(@i as nvarchar(2))+'] int,
[Pfoutvalue'+cast(@i as nvarchar(2))+'] int,
[PfSumPayedValue'+cast(@i as nvarchar(2))+'] bigint
'
exec sp_executesql @sql
set @sql='update #Out set Pfname'+cast(@i as nvarchar(2))+'='''+@Pfname+''',
Pfvalue'+cast(@i as nvarchar(2))+'='+cast(@Pfvalue as nvarchar(30))+',
Pfprimeryvalue'+cast(@i as nvarchar(2))+'='+cast(@Pfprimeryvalue as nvarchar(30))+',
Pfsecondaryvalue'+cast(@i as nvarchar(2))+'='+cast(@Pfsecondaryvalue as nvarchar(30))+',
Pfoutvalue'+cast(@i as nvarchar(2))+'='+cast(@Pfoutvalue as nvarchar(30))+',
PfSumPayedValue'+cast(@i as nvarchar(2))+'='+cast(@PfSumPayedValue as nvarchar(30))+',
Karkerd=dbo.FxPAY_HistoryDetailAmount('+cast(@PersonID as nvarchar(50))++','+cast(@MonthId as nvarchar(50))+',10,2)
where TBL_PersonnelID='+cast(@PersonID as nvarchar(50))++' and Pay_MonthID_fk='+cast(@MonthId as nvarchar(50))+'and Pay_PayedMonthID_fk='+cast(@PayedMonthId as nvarchar(50))+'and pay_PhbNote='''+@Pay_phbNote+''''
exec sp_executesql @sql
FETCH NEXT FROM Cursor_Pay
INTO @Pfname,@Pfvalue,@Pfprimeryvalue,@Pfsecondaryvalue,@Pfoutvalue,@PfSumPayedValue,@PAY_PhbOutFinal
END
CLOSE Cursor_Pay
DEALLOCATE Cursor_Pay
update #Out
set Nakhales=@SumFinalOut
where TBL_PersonnelID=@PersonID and Pay_MonthID_fk=@MonthId and Pay_PayedMonthID_fk=@PayedMonthId and pay_PhbNote=@Pay_phbNote
DROP TABLE #Pay
FETCH NEXT FROM Cursor_Primary
INTO @PersonID,@PayedMonthId,@MonthId,@Pay_phbNote
end
CLOSE Cursor_Primary
DEALLOCATE Cursor_Primary
select * from #Out
end
this one 1 affected and then return error message:
Could not complete cursor operation because the table schema changed after the cursor was declared.
please help me.what do i do?
December 28, 2011 at 8:05 am
WOW I don't know where to begin...so let's start at the top. The first thing that jumps off the screen screaming is @Where, @WherePay and @sort as parameters. This is executed against your database via dynamic sql. Familiar with the term sql injection? This procedure is wide open and ripe for the picking. I could pass you some pretty nasty value for @Where, @WherePay or @sort that could drop tables, database, delete data etc very easily.
Now let's discuss your cursors. Cursors are one of the least scalable ways you can manage data. They are horribly slow. There are a few, and I mean very few, instances where cursors are unavoidable. This is not one of them.
This whole process the way it is coded is going to be slow. If it isn't yet, it will be when there is more and more data. By slow I don't mean in the 5-8 seconds slow. I am thinking more like 20-30 minutes slow as the amount of data ramps up. Once you get to 100,000 rows you might as well go to lunch while this runs.
I think what you are trying to accomplish is a dynamic cross tab. This is not easy to pull off as you are finding out. It can however be done set based and eliminate the performance killing cursors. Take a look at these two articles from Jeff Moden. The first is one is on cross tabs[/url], the second one is dynamic cross tabs[/url].
Hope this helps gets you pointed in a direction to solve your issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply