1. Firstofall 5 variables are declared
declare @Pay int, @CycDate datetime, @PayCycle varchar(50), @TimeProcessDate datetime, @Begin_Dt datetime
2. Each variable is assigned to a value using set statement.
SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')
Assigning greatest ID to @pay from PayFeCon table which have status 'F'
SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')
Assigning CycleDate to @CycDate FROM PayFeCon table which have given ID and status 'F' i.e it should satisfy both conditions.
SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)
Assigning Pay_Cycle to @PayCycle from Period table which have given end date
SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)
Assigning Process_Date to @TimeProcessDate from Calender table which have given Payroll_Cycle
SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)
Assigning Begin_Dt to @Begin_Dt from Calender table which have given Payroll_Cycle
3. To select all the variables, Use this below select statement after running the set statement
<pre>select @Pay, @CycDate, @PayCycle, @TimeProcessDate, @Begin_Dt</pre>
4. For assigning a value to a variable both set and select statement can be used.
However, Set accept scalar value only , whereas select statement accept multiple value.
Hence, Set statement is recommended to assign a scalar value for a variable.