March 20, 2006 at 2:02 am
Hi Friends
I am trying to write a stored procedure for Employee Leave Package where each and every employee is given fixed amt of leave to avail evary 6 monthly If employee is retiring in that half year, he shall get credit at rate of 5/3 of remaining yr of service.
I am counting the duration if 6 months then add more Predefined no of days in the available balance somehow i am not getting final sum in the variable to update the balance in database !!
I am getting result @mLeft as NULL instead of integer number
Can anyone tell me how to typecast the temporary variables to integer number so that i can get integer sum SP is as follows:
CREATE PROCEDURE [dbo].[leave_update] AS
declare @eid int
declare @mid int
declare @mDETid int
declare @hpl int
declare @el int
declare @Cl int
declare @detid1 int
declare @detid2 int
declare @detid3 int
declare @Chpl int
declare @Cel int
declare @Ccl int
declare @MAXhpl int
declare @MAXel int
declare @MAXcl int
declare @dnon int
declare @LND int
declare @mLeft int
declare @lastCrdt datetime
declare @NxtCrdt datetime
declare @Retire datetime
declare cur cursor scroll static
for select leavepolid from dbo.leavepolreg1 open cur
fetch next from cur into @mid
WHILE (@@FETCH_STATUS=0)
BEGIN
select @mdetid=leavepolid , @eid =employeeid from leavepol where leavepolid=@mid
select @hpl=LEAVEBALANCE , @detid1=leavepoldetid from leavepoldet where leavepolid=@mdetid and leavetypeid=3
select @CL=LEAVEBALANCE , @detid2=leavepoldetid from leavepoldet where leavepolid=@mdetid and leavetypeid=1
select @EL=LEAVEBALANCE , @detid3=leavepoldetid from leavepoldet where leavepolid=@mdetid and leavetypeid=2
SELECT @CCL=CARRYFORWARD , @MAXCL=MAXATATIME FROM LEAVETYPE WHERE LEAVETYPEID=2
SELECT @CHPL=CARRYFORWARD , @MAXHPL=MAXATATIME FROM LEAVETYPE WHERE LEAVETYPEID=3
SELECT @CEL=CARRYFORWARD , @MAXEL=MAXATATIME FROM LEAVETYPE WHERE LEAVETYPEID=1
SET @HPL = @HPL + @CHPL
SET @CL = @CCL
SET @EL = @EL + @CEL
set @el = @el
if @el >315
begin
set @el= 315
end
/*Reduce LND and Deis Non Period from HPL*/
select @dnon=total from dp_leaveCreditDates where leavetypeid=15 and approve=1 and employeeid=@eid and datediff(d,PrevCredit,fromdate)>=0 and datediff(d,fromdate,LastCredit)>=0
select @LND=total from dp_leaveCreditDates where leavetypeid=12 and approve=1 and employeeid=@eid and datediff(d,PrevCredit,fromdate)>=0 and datediff(d,fromdate,LastCredit)>=0
set @HPL = @HPL - @dnon/18
set @HPL = @HPL - @LND
/*Reduce Amt Of Credit who is preparatory to Retirement*/
/*LastCrdt = lastcredited date , @retire is date of retirement */
select @Retire=superannuationDate from employee where employeeid=@eid
select @lastCrdt=LastCredit , @NxtCrdt=NxtCredit from dp_leaveCreditDates where employeeid=@eid
if datediff(d,@lastCrdt,@retire) >=0
begin
set @mLeft = ( datediff(m,@lastCrdt,@retire) )
set @HPL = @HPL - @chpl + (5/3) * @mLeft
end
UPDATE LEAVEPOLDET SET LEAVEBALANCE=@HPL WHERE LEAVEPOLID=@MDETID AND leavepoldetid= @detid1 and LEAVETYPEID=3
UPDATE LEAVEPOLDET SET LEAVEBALANCE= @CL WHERE LEAVEPOLID=@MDETID and leavepoldetid= @detid2 AND LEAVETYPEID=1
UPDATE LEAVEPOLDET SET LEAVEBALANCE= @el WHERE LEAVEPOLID=@MDETID AND leavepoldetid= @detid3 and LEAVETYPEID=2
FETCH NEXT FROM CUR INTO @MID
end
close cur
deallocate cur
GO
March 20, 2006 at 4:20 am
For that you have to use : isnull() function. to handle nulls.
Regards ,
Amit Gupta
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply