I have created a Stored procedure in which I am confused with the syntax whether properly closed or not.
For ex: for BEGIN/ END
Below is the procedure.
ALTER PROCEDURE [dbo].[Final_Leave_Allocate_CuttOff]
@Type nvarchar(10),
@Month int,
@Year int
AS
BEGIN
declare @actualMonth int
declare @actualYear int
declare @actuallastdate varchar(20)
declare @DAMonth int
declare @DAYear int
Declare @Final_LOP as numeric(5,2)
Declare @Final_LOPDays as numeric(5,2)
declare @checkRecord int
declare @NoOfDaysForDelayApproval numeric(5,2)
declare @Leave_Default numeric(5,2)
declare @status varchar(10)
declare @resig_date datetime
declare @dt_of_leave datetime
declare @emp_name varchar(20)
declare @dt_of_join datetime
declare @emp_card_no numeric(9)
declare @pl_days numeric(5,2)
declare @pl_days_opening numeric(5,2)
declare @Month_Diff int
declare @month1 numeric(5,2)
declare @month2 numeric(5,2)
declare @month3 numeric(5,2)
declare @month4 numeric(5,2)
declare @month5 numeric(5,2)
declare @month6 numeric(5,2)
declare @month7 numeric(5,2)
declare @month8 numeric(5,2)
declare @month9 numeric(5,2)
declare @month10 numeric(5,2)
declare @month11 numeric(5,2)
declare @month12 numeric(5,2)
declare @pl_sum numeric(5,2)
declare @Total_days numeric(5,2)
declare @Days_worked numeric(5,2)
declare @actualleavedays numeric(5,2)
declare @Final_PaidDayLop numeric(5,2)
declare @Opg_bal numeric(5,2)
declare @remaingPL as numeric(5,2)
declare @DelayedMonth int
declare @DelayedYear int
declare @LOP_value as numeric(5,2)
declare @emp_type varchar(2)
declare @emp_mkey int
declare @PL_Opening_2015 numeric(10,2)
declare @TOTAL_PL_Allocation_2015 numeric(10,2)
declare @PL_Alloc_2015_Month1 numeric(10,2)
declare @PL_Alloc_2015_Month2 numeric(10,2)
declare @PL_Alloc_2015_Month3 numeric(10,2)
declare @PL_Alloc_2015_Month4 numeric(10,2)
declare @PL_Alloc_2015_Month5 numeric(10,2)
declare @PL_Alloc_2015_Month6 numeric(10,2)
declare @PL_Alloc_2015_Month7 numeric(10,2)
declare @PL_Alloc_2015_Month8 numeric(10,2)
declare @PL_Alloc_2015_Month9 numeric(10,2)
declare @PL_Alloc_2015_Month10 numeric(10,2)
declare @PL_Alloc_2015_Month11 numeric(10,2)
declare @PL_Alloc_2015_Month12 numeric(10,2)
declare @TOTAL_OL_Alloc_2015 numeric(10,2)
declare @OL_Alloc_2015_MONTH1 numeric(10,2)
declare @OL_Alloc_2015_MONTH2 numeric(10,2)
declare @OL_Alloc_2015_MONTH3 numeric(10,2)
declare @OL_Alloc_2015_MONTH4 numeric(10,2)
declare @OL_Alloc_2015_MONTH5 numeric(10,2)
declare @OL_Alloc_2015_MONTH6 numeric(10,2)
declare @OL_Alloc_2015_MONTH7 numeric(10,2)
declare @OL_Alloc_2015_MONTH8 numeric(10,2)
declare @OL_Alloc_2015_MONTH9 numeric(10,2)
declare @OL_Alloc_2015_MONTH10 numeric(10,2)
declare @OL_Alloc_2015_MONTH11 numeric(10,2)
declare @OL_Alloc_2015_MONTH12 numeric(10,2)
declare @Mkey int
declare @Entry_Sr_no int
declare @dcount int
declare @TotalRecord int
declare @date_of_joining DATETIME
declare @setCount INT
declare @OnDate DATETIME
SET NOCOUNT ON;
set @Leave_Default=1.75
IF (@Month = 1)
BEGIN
set @actualYear = @Year - 1
set @actualMonth = 12
set @DelayedMonth = 11
set @DelayedYear = @Year - 1
END
ELSE
BEGIN
set @actualYear = @Year
set @actualMonth = @Month - 1
IF (@Month = 2)
BEGIN
set @DelayedMonth = 12
set @DelayedYear = @Year - 1
END
Else
BEGIN
set @DelayedMonth = @actualMonth - 1
set @DelayedYear = @Year
END
END
Print 'Actual Year ' + cast(@actualYear as VARCHAR)
Print 'Actual Month ' + Cast(@actualMonth as varchar)
Print 'Delayed Year ' + Cast(@DelayedYear as varchar)
Print 'Delayed Month' + Cast(@DelayedMonth as varchar)
DECLARE @DATE DATETIME;
SET @DATE = CAST(CAST(@actualYear AS VARCHAR)+'-'+CAST(@DelayedMonth AS VARCHAR)+'-'+ Cast(Day(DATEADD(DAY,-1,DATEADD(month,@DelayedMonth,DATEADD(year,@actualYear-1900,0)))) AS VARCHAR) AS DATETIME);
PRINT Convert(varchar(11),@DATE,103)
set @actuallastDate = convert(varchar(10),'1/'+Convert(Varchar,@actualMonth)+'/'+Convert(Varchar,@actualYear),103)
print @actuallastDate
IF(@Type = 'M')
BEGIN
Print 'Yes I am in the Monthly process'
SELECT @checkRecord = COUNT(Year) FROM p_leave_allocation where Year = @Year
IF(@checkRecord = 0)
BEGIN
Print 'Kindly Run Annual process first for creation of Blank Records for New Year.'
RETURN
END
declare monthly_Allocate cursor for
Select e.status,e.resig_date, dt_of_leave, e.emp_name,e.date_of_joining, e.emp_card_no,
a.pl_days,pl_days_opening,
a.month1,a.month2,a.month3,a.month4,a.month5,a.month6,a.month7,a.month8,a.month9,a.month10,a.month11,a.month12,--actual_pl,
a.month1+a.month2+a.month3+a.month4+a.month5+a.month6+a.month7+a.month8+a.month9++a.month10+a.month11+a.month12 pl_sum
from p_leave_allocation a join emp_mst e on a.emp_card_no=e.emp_card_no
where a.year=@actualYear
and (datediff(month,e.date_of_joining,convert(datetime,@actuallastDate,103)) >= 6
and datediff(month,e.date_of_joining,convert(datetime,@actuallastDate,103)) <= 36)
and (e.resig_date is null or (e.dt_of_leave is not null and e.dt_of_leave >= convert(datetime,@actuallastDate,103)))
and e.status in ('A','S')
and e.comp_mkey in (Select distinct m.comp_mkey from emp_mst e, company_mst c, P_Monthly_Must_Para_Hdr m where c.mkey = e.comp_mkey
and m.comp_mkey = e.comp_mkey and (m.process_date is not null
and (convert(varchar,(getDate())-5,103)) = convert(varchar,m.process_date, 103)))
order by 7,5
OPEN monthly_Allocate fetch next
FROM monthly_Allocate into
@status,@resig_date,@dt_of_leave,@emp_name, @dt_of_join,@emp_card_no,@pl_days,@pl_days_opening,
@month1,@month2,@month3,@month4,@month5,@month6,@month7,@month8,@month9,@month10,@month11,@month12,@pl_sum
WHILE @@FETCH_STATUS = 0
BEGIN
set @actualleavedays = 0
set @Final_PaidDayLop = 0
Print 'Faiz K.' + cast(@emp_card_no as varchar(10))
select @Total_days = Sum(total_day), @Days_worked = Sum(days_worked)
from emp_mon_day a
where a.emp_mkey = @emp_card_no
and a.month = @actualMonth and Year = @actualYear
group by emp_mkey
print @emp_card_no
print @Total_days
print @Days_worked
if(@Days_worked > 0)
BEGIN
Set @actualleavedays = (cast(@Leave_Default as numeric(18,2))/cast(@Total_days as numeric(18,2)))* cast(@Days_worked as numeric(18,2))
END
print @actualleavedays
DECLARE @sql NVARCHAR(MAX)
PRINT @sql
EXEC SP_EXECUTESQL @sql;
BEGIN
declare @totaldaysofmonth decimal(9,2)
set @totaldaysofmonth = DAY(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)))
select @NoOfDaysForDelayApproval=isnull(sum(Total_Days),0.00)
from XXACL_EMP_DELAY_APPROVAL_V
where DYear = @DelayedYear and DMonth = @DelayedMonth
and CardNo = @emp_card_no
print @NoOfDaysForDelayApproval
declare @DelayleaveAllocate decimal(9,2)
set @DelayleaveAllocate = ((1.75 / @totaldaysofmonth) * @NoOfDaysForDelayApproval)
print @DelayleaveAllocate
SET @sql = '';
print 'Yes 01'
print @DelayedMonth
declare @strmon varchar(100)
set @strmon= 'cast(month'+ CAST(@DelayedMonth AS VARCHAR(100)) + ' as numeric(10,2)) + cast(' + cast(@DelayleaveAllocate as varchar(50)) +' as numeric(10,2))'
print @DelayleaveAllocate
PRINT 'EMPLOYEE DATA ' + cast(@emp_name as char)
END
FETCH NEXT
FROM monthly_Allocate into
@status,@resig_date,@dt_of_leave,@emp_name, @dt_of_join,@emp_card_no,@pl_days,@pl_days_opening,
@month1,@month2,@month3,@month4,@month5,@month6,@month7,@month8,@month9,@month10,@month11,@month12,@pl_sum
END
close monthly_Allocate
deallocate monthly_Allocate
END -- END
ELSE IF(@Type = 'C')
BEGIN
Print 'Yes I am in the Current process';
set @OnDate = '2016-03-14';
DECLARE daily_Allocate CURSOR FOR
Select distinct c.emp_card_no, c.emp_name, c.Date_Of_Joining from emp_mst c
join emp_mon_day d
on c.emp_card_no=d.emp_mkey
WHERE Date_Of_Joining = CAST(FLOOR(CAST( DATEADD(month, -6, @OnDate) AS FLOAT ))AS DATETIME)
OPEN daily_Allocate
FETCH NEXT FROM daily_Allocate INTO
@emp_card_no, @emp_name, @Date_Of_Joining
WHILE @@FETCH_STATUS = 0
BEGIN
select @Total_days = Sum(total_day),@Days_worked = Sum(days_worked)
from emp_mon_day a
where a.emp_mkey = @emp_card_no
group by emp_mkey
PRINT 'Employee Card no = ' + cast(@emp_card_no as char)
PRINT 'Total days = ' + cast(@Total_days as char)
PRINT 'Days Worked = ' + cast(@Days_worked as char)
set @Final_PaidDayLop = 0;
set @TotalRecord = 0;
Select @Final_PaidDayLop =
isnull(sum(days),0)
from P_Emp_Del_App_Hdr c join P_Emp_Del_App_trl d on c.mkey=d.mkey
where c.delete_flag='N' and app_flag='Y' and c.year = @Year
and c.emp_mkey = @emp_card_no
set @setCount = 0;
Select @setCount = count(*) from emp_mon_day
where emp_mkey =@emp_card_no
Select @TotalRecord = ((1.75 * @setCount) / @Total_days) * (@Days_worked + @Final_PaidDayLop)
from emp_mon_day a where a.emp_mkey = @emp_card_no group by a.emp_mkey
PRINT 'Final Paid LOP ' + cast(coalesce(@Final_PaidDayLop,0) as char)
PRINT 'Total Record ' + cast(coalesce(@TotalRecord,0) as char)
PRINT 'Set Count ' + cast(coalesce(@setCount,0) as char)
FETCH NEXT FROM daily_Allocate INTO @emp_card_no, @emp_name, @Date_Of_Joining
END
CLOSE daily_Allocate
DEALLOCATE daily_Allocate
END END
Kindly let me know if there is mistake in Opening and closing of BEGIN/ END OR ELSE/IF
Aucun commentaire:
Enregistrer un commentaire