How to Select a Source Table Dynamically with a Parameter in Stored Procedure of SQL Server

How to Dynamically Select a Source Table with a Parameter and Load Data to the Final Reporting Table using a Stored Procedure in SQL Server

Scenario :

Suppose we have 4 source tables which have data for respective 4 years, descending from Current Year..CFY ( eg: 2018) , PFY1(eg: 2017), PFY2(eg: 2016), PFY3(eg: 2015).

Whenever the Source data for a specific Year is ready, we would like to load it to the Final Reporting Table by dynamically selecting the data from that respective source table based on the Parameter we Pass in to the SP.

Example :  

Exec  [dbo].[SP_UpdateReportingData] 'PFY2'

In the above the SP will update the PFY2 (2016) data from the respective Source Table 

"[dbo].[TBL_MySample_Source_Data_PrevFY2]" Dynamically to the Final Reporting Table 

" [dbo].[TBL_MyFinal_Reporting_Data]"

======================================

USE [TPREDDY_DB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

======================================

-- Created By            : <tpreddy123>

-- Create On             : <19thAPR2018>

-- Last Modified By : <tpreddy143>

-- Last Modified On : <19thAPR2018>

-- Description           : <Consolidate and loads the specific Year data to                                                                      Reporting Table>

======================================

CREATE PROCEDURE [dbo].[SP_UpdateReportingData] (@sYear as Varchar(10))    

AS    

DECLARE @Update_FY AS INT  

DECLARE @Tbl_Name  AS SysName   

DECLARE @DynSQL AS NVARCHAR(MAX)

BEGIN    

--Storing the Fiscal Year value based on Parameter value

SET  @Update_FY=

    CASE WHEN @sYear='CFY' THEN  YEAR(GETDATE()) 

WHEN @sYear='PFY1' THEN  YEAR(GETDATE())-1 

WHEN @sYear='PFY2' THEN  YEAR(GETDATE())-2

WHEN @sYear='PFY3' THEN  YEAR(GETDATE())-3   

END 

--PRINT(@Update_FY)

--Storing the Table Name to SysName Variable

SET  @Tbl_Name=
    CASE WHEN @sYear='CFY'   THEN  '[dbo].[TBL_MySample_Source_Data_Cur_FY]'

WHEN @sYear='PFY1'   THEN '[dbo].[TBL_MySample_Source_Data_PrevFY1]'

WHEN @sYear='PFY2'   THEN '[dbo].[TBL_MySample_Source_Data_PrevFY2]'

WHEN @sYear='PFY3'   THEN '[dbo].[TBL_MySample_Source_Data_PrevFY3]'  

END 

--Assigning the Dynamic SQL Statement to a Variable

Set @DynSQL='Select Distinct * From '+ @Tbl_Name

--Next Deleting the respective Year data from the Reporting Table

Delete from  [dbo].[TBL_TBL_MyFinal_Reporting_ Data]   

                                        Where YEAR(Trans_Date )=@Update_FY

-- Next loading the respective Year Source data from a Sample Source Data Table

   INSERT INTO [dbo].[TBL_MyFinal_Reporting_Data]    

   EXEC sp_executesql @DynSQL  

----Eg: SELECT DISTINCT* FROM [dbo].[TBL_MySample_Source_Data_PrevFY2]

END

GO

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------