How to Dynamically Pass a Value to a Variable during run time in a Stored Procedure of SQL Server

How to Pass a  value dynamically to a Variable during run time in a SP of SQL Server
The below is an example SP Created for Inserting YoY_QTD_Weekly data from the Base Table (which contains Current Year and Previous Year data) to Final table for Analyzing the data (YoY-QTD-Weeks) as follows :
Eg :  [ 2016-Q3-W1]   vs. [2017-Q3-W1 ]
         [ 2016-Q3-W1+W2+.. ]  vs. [ 2017-Q3-W1+W2+..]

USE TAMATAM
GO

CREATE PROC [SP_YoY_QTD_Weekly_Analysis]
AS
Declare @Cur_Wk as Varchar(50)=NULL, @Cur_FY AS INT=NULL;
BEGIN

Truncate Table [Tbl_QTD_YoY_By_Weeks_Data_Final];

--Dynamically setting/assigning the values to the Variables during run-time

SET @Cur_FY = (Select Max([Year]) as Cur_FY From [Tbl_YoY_QTD_By_Weeks_Data])

SET @Cur_Wk = (Select Max(Week_Id) From 
                                                  [Tbl_YoY_QTD_By_Weeks_Data] 
                                          WHERE [Year]=@Cur_FY)

--PRINT(@Cur_FY)
--PRINT(@Cur_Wk)

--Setting the Value to a Variable as the Concatenation two strings.
Set @Cur_Wk=CAST((LEFT(@Cur_Wk,4)-1) AS VARCHAR(20))+ CAST(RIGHT(@Cur_Wk,3) AS VARCHAR(20))

--Converting the String Variable to Integer
Set @Cur_Wk=CAST(@Cur_Wk AS INT)

--Inserting the Data from Base table to Final Table for YoY_QTD_By_Weeks Analysis 
INSERT INTO [Tbl_YoY_QTD_By_Weeks_Data_Final]
SELECT [Year],[SalesType],[Sales_Region 1],[Sales_Region 2] ,[Sales_Region 3] ,
                [Prod Type],[Segment],[Net Sales] 
FROM ( SELECT * FROM [Tbl_YoY_QTD_By_Weeks_Data] 
                WHERE Week_Id<=@Cur_Wk AND [Fiscal Year]=(@Cur_FY-1)
UNION ALL
SELECT * FROM [Tbl_YoY_QTD_By_Weeks_Data] WHERE [Fiscal Year]=@Cur_FY
     )S1
/*
--Check :
--SELECT * FROM [Tbl_QTD_YoY_By_Weeks_Data] WHERE Week_Id<=@Cur_Wk AND [Year]=@Cur_FY-1
--SELECT DISTINCT Week_Id
FROM ( 
                 SELECT * FROM [Tbl_YoY_QTD_By_Weeks_Data] WHERE                                                           Week_Id<=@Cur_Wk AND [Year]=@Cur_FY-1
UNION ALL
          SELECT * FROM [Tbl_YoY_QTD_By_Weeks_Data] WHERE [Year]=@Cur_FY
         )S1
*/

SELECT * FROM [dbo].[Tbl_YoY_QTD_By_Weeks_Data_Final]

END
GO

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------