Thursday, 19 April 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
--------------------------------------------------------------------------------------------------------

1 comment:

  1. This really has covered a great insight on Sql Server . I found myself lucky to visit your page and came across this insightful read on Sql Server tutorial. Please allow me to share similar work on MSBI training course:-
    https://www.youtube.com/watch?v=mrv0e6Qa4_M

    ReplyDelete

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts