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)  pls help mi to avoid cursor in my procedure

Author  Topic  umar Starting Member 6 Posts Posted - 2009-01-22 : 05:54:35 this is ma procedure ALTER PROCEDURE [dbo].[lntsp_Csti_Hr_Stipend_Last_Report] ( @Trade_Code varchar(15)=null, @CstiCode VARCHAR(15)= NULL, @SelYear int=null, @SelMonth int=null, @SelFN int=null, @StipendFrom VARCHAR(50)= NULL, @StipendTo VARCHAR(50)= NULL, @LeavingDate varchar(50)=null, @uid int=0, @error VARCHAR(100)=null OUTPUT ) AS SET NOCOUNT ON /* Created By : umar Created On : 2-jan-2009 Purpose : lntsp_Csti_Hr_Stipend_Last_Report */ BEGIN ---Creating Temparay table for finding Extra holiday based on P&OD create table #temp_Extra_Holiday_L (trainee_Id varchar(15),Extra_Holiday int) ----get set of trainee id based condition declare @TraineeID varchar(15) declare @ExdL int select REPLACE(CONVERT(CHAR(11), holiday_date, 113),space(1),'-') as holiday_date into #Csti_Hr_Holiday_Master1 from Csti_Hr_Holiday_Master where csticode=@CstiCode and (holiday_date BETWEEN @StipendFrom and @StipendTo) DECLARE @NoofDays int set @NoofDays = datediff(dd ,@StipendFrom ,@StipendTo)+1 insert into #Csti_Hr_Holiday_Master1 (holiday_date) select REPLACE(CONVERT(CHAR(11), DATEADD(dd, M.number-1, @StipendFrom), 113),space(1),'-') FROM master..spt_values AS M WHERE M.type = 'P' and datename(dw,DATEADD(dd, M.number-1, @StipendFrom))='Sunday' and M.number BETWEEN 1 AND @NoofDays select distinct(holiday_date) into #Csti_Hr_Holiday_Master from #Csti_Hr_Holiday_Master1 select tr.trainee_id into #temp_trainee_id_GRL from Csti_Hr_Trainees tr where tr.Joined_Csti_Code=@CstiCode and tr.joined_trade_code=@Trade_Code and ( (@StipendFrom between tr.Joining_Date and tr.Leaving_Date) OR (@StipendTo between tr.Joining_Date and tr.Leaving_Date)) and (tr.leaving_date between @StipendFrom and @StipendTo) and tr.leaving_date=@LeavingDate ----ctreate cursor for that set of trainee id declare cursor_tid cursor for select trainee_id from #temp_trainee_id_GRL order by trainee_id --open & fetch cursor open cursor_tid fetch cursor_tid into @TraineeID --FETCH NEXT FROM cursor_po INTO @po_code while (@@FETCH_STATUS = 0) begin --print @TraineeID ----get set of Absent date based condition declare @Absent_dateL varchar(50) declare @tcL int set @ExdL=0 select Absent_date into #temp_absent_date_L from Csti_Hr_Trainee_Absent_Register tr where tr.trainee_id=@TraineeID AND (Absent_date BETWEEN @StipendFrom and @StipendTo) -- DECLARE @NoofDays int -- set @NoofDays = datediff(dd ,@StipendFrom ,@StipendTo)+1 -- -- insert into #temp_absent_date_L (Absent_date) -- select REPLACE(CONVERT(CHAR(11), DATEADD(dd, M.number-1, @StipendFrom), 113),space(1),'-') -- FROM master..spt_values AS M -- WHERE M.type = 'P' and datename(dw,DATEADD(dd, M.number-1, @StipendFrom))='Sunday' and -- M.number BETWEEN 1 AND @NoofDays ----ctreate cursor for that set of Absent dates declare cursor_ad cursor for select Absent_date from #temp_absent_date_L order by Absent_date --open & fetch cursor open cursor_ad fetch cursor_ad into @Absent_dateL while (@@FETCH_STATUS = 0) begin --print @Absent_dateL DECLARE @FlagL INT SET @FlagL = 0 declare @holiday_dateL datetime set @holiday_dateL=dateadd(day,1,@Absent_dateL) IF(select holiday_date from #Csti_Hr_Holiday_Master where holiday_date=@holiday_dateL ) IS NOT NULL --csticode=@CstiCode and and (holiday_date BETWEEN @StipendFrom and @StipendTo) BEGIN WHILE (select holiday_date from #Csti_Hr_Holiday_Master where holiday_date=@holiday_dateL) is not null --csticode=@CstiCode and BEGIN SET @FlagL = @FlagL + 1 set @holiday_dateL=dateadd(day,1,@holiday_dateL) END -- PRINT '-FINAL' if(select Absent_date from Csti_Hr_Trainee_Absent_Register where trainee_id=@TraineeID and Absent_date=@holiday_dateL) is null -- PRINT @FlagL -- ELSE -- SET @FlagL=0 begin SET @FlagL=0 end END --print @FlagL--------------extra --Add the number of extra days set @ExdL=@ExdL+@FlagL FETCH NEXT FROM cursor_ad INTO @Absent_dateL --select @TraineeID,@FlagL end close cursor_ad deallocate cursor_ad drop table #temp_absent_date_L --print @ExdL-------total extra --insert Extra days into temp table insert into #temp_Extra_Holiday_L select @TraineeID,@ExdL FETCH NEXT FROM cursor_tid INTO @TraineeID end --close the cursor close cursor_tid deallocate cursor_tid