In SQL while writing stored procedures sometimes you need variables for calculations and manipulations.

Variable we can use of two type

1 Local

2 Global

ALTER proc [dbo].[sp_CalculateRemainingLeaves]
 
 (
 @EmployeeID int
 )
 as

 begin


declare @GetPLID int
declare @countPL int
declare @TotalleavestakenPL decimal
declare @TotalPL decimal
declare @TotalleavesavailablePL decimal


declare @GetCLID int
declare @countCL int
declare @TotalleavestakenCL decimal
declare @TotalCL decimal
declare @TotalleavesavailableCL decimal






declare @GetRHID int
declare @countRH int
declare @TotalleavestakenRH int
declare @TotalRH int
declare @TotalleavesavailableRH int



set @TotalPL = 12
set @GetPLID=(select EmployeeLeaveId from dbo.EmployeeLeaveCategory where EmployeeLeaveType='PL')
set @countPL=(select count(*) from EmployeeLeaveDetails where EmployeeId=@EmployeeID and EmployeeLeaveID=@GetPLID)
if  (@countPL>0)
begin
	set @TotalleavestakenPL=(select sum(EmployeeLeaveDuration) from EmployeeLeaveDetails where EmployeeId=@EmployeeID 

and EmployeeLeaveID=@GetPLID)
end
else
begin
	set @TotalleavestakenPL=0
end
set @TotalleavesavailablePL=@TotalPL-@TotalleavestakenPL





set @TotalCL = 12
set @GetCLID=(select EmployeeLeaveId from dbo.EmployeeLeaveCategory where EmployeeLeaveType='CL')
set @countCL=(select count(*) from EmployeeLeaveDetails where EmployeeId=@EmployeeID and EmployeeLeaveID=@GetCLID)
if  (@countCL>0)
begin
	set @TotalleavestakenCL=(select sum(EmployeeLeaveDuration) from EmployeeLeaveDetails where EmployeeId=@EmployeeID 

and EmployeeLeaveID=@GetCLID)
end
else
begin
	set @TotalleavestakenCL=0
end
set @TotalleavesavailableCL=@TotalCL-@TotalleavestakenCL





set @TotalRH = 1
set @GetRHID=(select EmployeeLeaveId from dbo.EmployeeLeaveCategory where EmployeeLeaveType='RH')
set @countRH=(select count(*) from EmployeeLeaveDetails where EmployeeId=@EmployeeID and EmployeeLeaveID=@GetRHID)
if  (@countRH>0)
begin
	set @TotalleavestakenRH=(select sum(EmployeeLeaveDuration) from EmployeeLeaveDetails where EmployeeId=@EmployeeID 

and EmployeeLeaveID=@GetRHID)
end
else
begin
	set @TotalleavestakenRH=0
end
set @TotalleavesavailableRH=@TotalRH-@TotalleavestakenRH




select @TotalleavesavailablePL as 'TotalPLAvailable',@TotalleavesavailableCL as 'TotalCLAvailable',@TotalleavesavailableRH 

as 'TotalRHAvailable'



end

In this example i am calculating the PL CL and RH of employees . I am using both local and global variables for that