Here I have Created Sample Using Variable Table Name as of Yours,
In this sample I'm using EmplyeeId Variable that takes EmployeeID,
And Checks weather Employee has been reached his Leave or not (i.e. Vacation Leave = 5 or Sick leave = 5)
I hope this will help you out.
SQL
DECLARE @TBLLeave AS TABLE(Id INT IDENTITY(1,1),EmployeeId INT,Date DATETIME,Year INT,LeaveID INT,NoofDays INT,Status INT)
INSERT INTO @TBLLeave(EmployeeId,Date,Year,LeaveID,NoofDays,Status) VALUES(101,GETDATE(),DATEPART(YEAR,GETDATE()),1,1,10)
INSERT INTO @TBLLeave(EmployeeId,Date,Year,LeaveID,NoofDays,Status) VALUES(102,GETDATE(),DATEPART(YEAR,GETDATE()),2,5,11)
INSERT INTO @TBLLeave(EmployeeId,Date,Year,LeaveID,NoofDays,Status) VALUES(103,GETDATE(),DATEPART(YEAR,GETDATE()),1,6,12)
INSERT INTO @TBLLeave(EmployeeId,Date,Year,LeaveID,NoofDays,Status) VALUES(101,GETDATE(),DATEPART(YEAR,GETDATE()),2,2,13)
INSERT INTO @TBLLeave(EmployeeId,Date,Year,LeaveID,NoofDays,Status) VALUES(101,GETDATE(),DATEPART(YEAR,GETDATE()),2,4,13)
DECLARE @RefLeaveType AS TABLE(LeaveId INT IDENTITY(1,1),LeaveType char(15))
INSERT INTO @RefLeaveType(LeaveType) VALUES('SICK LEAVE')
INSERT INTO @RefLeaveType(LeaveType) VALUES('VACATION LEAVE')
DECLARE @RefLeaveStatus AS TABLE(LeaveStatusID INT IDENTITY(10,1),StatusName CHAR(10))
INSERT INTO @RefLeaveStatus(StatusName) VALUES('Approved')
INSERT INTO @RefLeaveStatus(StatusName) VALUES('Rejected')
DECLARE @TBLLeaveEarned AS TABLE(EarnedID INT IDENTITY(1,1),EmployeeId INT,Sick INT,Vacation INT)
--=======Check Vacation Or Sick Leave Before Insert Start Here========---
DECLARE @EmployeeID INT
SET @EmployeeID = 101
INSERT INTO @TBLLeaveEarned(EmployeeId,Sick,Vacation)
SELECT EmployeeId
,(SELECT NoofDays WHERE t1.LeaveId = 1 AND EmployeeId = t1.EmployeeId)
,(SELECT NoofDays WHERE t1.LeaveId = 2 AND EmployeeId = t1.EmployeeId)
FROM @TBLLeave t1
DECLARE @SickCOUNT INT --Variable hold the SickCount
DECLARE @VacationCount INT --Variable hold the VacationCount
SELECT @SickCOUNT = SUM (Sick)
,@VacationCount = SUM(Vacation)
FROM @TBLLeaveEarned T
WHERE EmployeeId = @EmployeeId
GROUP BY T.EmployeeID
IF (@SickCOUNT < 5 AND @VacationCount < 5)
BEGIN
--Write Here Script to insert Data in Table
select 'INSERTED'
END
ELSE
BEGIN
select 'NOT'
END