Wednesday, February 8, 2017

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

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