While working in SQL, you always write queries and stored procedures and these things needs to done in a better and go through the conventions manner so that the understandability of your script will be more.

Rules while writing queries in SQL:

Always use the CamelCasing while declaring variables

ALTER  PROCEDURE [dbo].[sp_AddEmployee]
(
@empName VARCHAR(80),
@empDesgnationId INT,
@empDob DATE,
@empSal MONEY,
@empDoj DATE,
@empEmail varchar(100),
@empManagerId INT ,
@empLogUserName VARCHAR(100),
@empLogUserPassword VARCHAR(100)

)
AS
BEGIN

DECLARE @flag INT

SET @flag=(SELECT 1 FROM dbo.EmployeeDetails WHERE EmployeeEmail=@empEmail)

IF(@flag>0)
BEGIN
DECLARE @empID INT

INSERT INTO dbo.EmployeeDetails(EmployeeName,EmployeeDesignationId,EmployeeDob,EmployeeSal,EmployeeDoj,EmployeeEmail,EmployeeManagerId) values (@empName,@empDesgnationId,@empDob,@empSal,@empDoj,@empEmail,@empManagerId)
SET @empID=SCOPE_IDENTITY()
INSERT INTO dbo.EmployeeLogin (EmployeeId,EmployeeLoginUserName,EmployeeLoginPassword) VALUES (@empID,@empLogUserName,@empLogUserPassword)
SELECT 1
END

ELSE
BEGIN
SELECT 2
END

END

Always use square brackets while referring table or column name.

INSERT INTO [dbo].[User]
        ([FirstName]
        ,[LastName]
        ,[JobRoleID]
        ,[Email]
        ,[CompanyID]
        ,[Password]
        ,[PasswordSalt]
        ,[AccessToken]
		,[DeviceUUID]
		,[DeviceType]
        ,[CreatedDate]
        ,[IsDeleted]
		,[DeletedDate])
    VALUES
        (@FirstName
        ,@LastName
        ,@JobRoleID
        ,@Email
        ,@CompanyID
        ,@EncryptedPassword
        ,@PasswordSalt
        ,@AccessToken
		,@DeviceUUID
		,@DeviceType
        ,@CreatedDate
		,@IsDeleted
        ,@DeletedDate)

Always use dbo databaseobject name while referring a table or creating a stored procedure.

ALTER proc [dbo].[sp_ApproveLeave]
(
@EmployeeID int,
@EmployeeLeaveApprovedBy varchar(50),
@EmployeeLeaveFromDate date,
@EmployeeLeaveToDate date
)
as

begin
update dbo.employeeleavedetails set employeeleavestatus='Approved',EmployeeLeaveApprovedBy=@EmployeeLeaveApprovedBy where EmployeeId=@EmployeeID and employeeleavefromdate=@EmployeeLeaveFromDate and Employeeleavetodate=@EmployeeLeaveToDate
end

While writing stored procedures only declare those variable global which needs to be passed as parameters otherwise declare it inside .

ALTER proc [dbo].[sp_CheckLeaveAvailability]		
@FromDate date,
@ToDate date,
@EmployeeID int,
@GetAvailablePL decimal,
@GetAvailableCL decimal,
@GetAvailableRH int ,
@LeaveReason varchar(200),
@LeaveType int



as

begin

Declare @TotalHolidays int ,@FinalLeaveDuration decimal,@CheckLeaveBalance int,@LeaveStatus varchar(20) ,@Duration decimal,@PaternityAvail binary = 0

set @Duration=(SELECT DATEDIFF(day,@FromDate,@ToDate));

set @TotalHolidays=(select count(*) from EmployeeHolidayDetails where EmployeeHolidayDetailDate between @FromDate and @ToDate ) 



if(@TotalHolidays>0)

begin

if(@TotalHolidays<=@Duration)
begin
set @FinalLeaveDuration=@Duration-@TotalHolidays
end

else
begin
set @FinalLeaveDuration=@TotalHolidays-@Duration
end


end




else

begin
set @FinalLeaveDuration=@Duration
end







if(@LeaveType=1)

begin


if(@FinalLeaveDuration<=@GetAvailablePL)

begin

set @LeaveStatus='Granted'


end


else

begin
set @LeaveStatus='Rejected'
end



end






if(@LeaveType=2)

begin


if(@FinalLeaveDuration<=@GetAvailableCL)

begin
set @LeaveStatus='Granted'
end


else

begin
set @LeaveStatus='Rejected'
end

end







if(@LeaveType=3)

begin

if(@FinalLeaveDuration<=@GetAvailableRH)

begin
set @LeaveStatus='Granted'
end


else

begin
set @LeaveStatus='Rejected'
end



end









if(@LeaveType=4)

begin
set @PaternityAvail=(select count(*) from dbo.EmployeeLeaveDetails where EmployeeLeaveId=@LeaveType and EmployeeId=@EmployeeId) 

if(@FinalLeaveDuration<=7 and @PaternityAvail=0)

begin
set @LeaveStatus='Granted'

end


else

begin
set @LeaveStatus='Rejected'
end



end







select @LeaveStatus as 'LeaveStatus',@TotalHolidays,@FinalLeaveDuration


end