Thursday, April 19, 2018

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.
Eg :  
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
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog