Wednesday, 18 October 2017

How to Update the Metadata of an Object in SQL Server

SQL SERVER sp_refreshsqlmodule to Update the Metadata of an Object
Whenever you modify the underlying objects (Tables, columns, datatypes etc) of any non-schema-bounded Stored Procedure, View or an User-defined Function, mostly due to customization then your object will not address those customization and might give you error or unexpected results.
To overcome this issue we need to refresh the Metadata of the underlying object whenever you do some alteration to them, as explained below:

Suppose we have a Table with two existing columns as follows :
Select*From Tbl_EMP;
GO

Emp_ID Emp_Name
1             A1
2             B2


Now based on the above table, I created a non schema-bounded View and a Function as follows :
CREATE VIEW dbo.V_Emp
AS
Select*From dbo.Tbl_EMP

GO

CREATE FUNCTION dbo.Fn_Emp( )
RETURNS TABLE
AS
RETURN
(Select*From dbo.Tbl_EMP)

GO
-------------------------------------------------------------------------------------------------
Later after a few days, I have modified my based table, where I added a new Column 'Address' with data as follows ..

ALTER TABLE Tbl_EMP Add Address Varchar(50)
Update dbo.Tbl_EMP Set Address='Bangalore' Where Emp_NAME ='A1'
Update dbo.Tbl_EMP Set Address='Hyderabad' Where Emp_NAME ='B2'

Now the underlying/base table "Tbl_EMP" has three columns.If you run below queries of the dependent View or Function, you will get the output as follows :
SELECT * FROM dbo.V_Emp
SELECT * FROM dbo.Fn_Emp ()

Output :
Emp_ID Emp_Name
1             A1
2             B2

The Newly added Column 'Address' is not shown in the output , as these are not refreshed with the Meta Data.
-------------------------------------------------------------------------------------------------
Now we need to refresh the Meta data for the dependent Function and View as follows :
EXEC sys.sp_refreshsqlmodule 'dbo.Fn_Emp'
EXEC sys.sp_refreshsqlmodule
'dbo.V_Emp'
GO

Now re-run below queries of the dependent View or Function


SELECT * FROM dbo.V_Emp
SELECT * FROM dbo.Fn_Emp ()

Now we will get the refreshed output as it is from base table :

Output :
Emp_ID  Emp_Name   Address
     1              A1           Bangalore
     2              B2           Hyderabad


-------------------------------------------------------------------------------------------------
Thanks, TAMATAM
-------------------------------------------------------------------------------------------------

How to Split a Single column into Multiple columns in SQL Server

XML Method to Split a Single column into Multiple columns in SQL Server
Suppose We have a Table called Tbl_EMP with columns Emp_Id , Emp_Name where the EMP_Name have the FirstName,MiddleName and LastName combined by the delimiter "_" underscore as follows :

Select*From Tbl_EMP;
GO

Emp_ID    Emp_Name
1               ABC_CDE_DE
2               FGH_HI
3               JKL_LMN_NOP
4               PQR_RS


Now if we wants to Split this Name into 3 Columns as First_Name,Middle_Name and Last_Name , we can do it as follows using the XML Method :

DECLARE @Delimiter VARCHAR(50)
SET @Delimiter='_';
   -- <=== Here, you can change the delimiter.
WITH CTE AS
(
    SELECT
        [Emp_ID],
        [Emp_Name],
        CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
        AS [Emp_Name XML]

    FROM  Tbl_EMP
)
SELECT
     [Emp_ID],
     [Emp_Name],
     [Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
  [Emp_Name XML].value('/D[2]', 'varchar(50)') As [Middle_Name],
     [Emp_Name XML].value('/D[3]', 'varchar(50)') As [Last_Name]
FROM CTE

GO
-----------------------------------------------------------------------------------------------------
Output :
-----------------------------------------------------------------------------------------------------
If you observe the above Output, which may not looks accurate for records 2 and 4, as they don't have the Middle Name which should be Last Name.

We can handle this Using the Case Statement in above query as follows :

DECLARE @Delimiter VARCHAR(50)
SET @Delimiter='_';
   -- <=== Here, you can change the delimiter.
WITH CTE AS
(
    SELECT
        [Emp_ID],
        [Emp_Name],
        CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
        AS [Emp_Name XML]
    FROM  Tbl_EMP )SELECT
     [Emp_ID],
     [Emp_Name],
     [Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
  CASE
       WHEN ([Emp_Name XML].value('/D[2]', 'varchar(50)') IS NOT NULL)
                     AND ([Emp_Name XML].value('/D[3]', 'varchar(50)') IS NULL)
       THEN NULL
                    ELSE [Emp_Name XML].value('/D[2]', 'varchar(50)')
  END AS [Middle_Name],
  CASE
         WHEN ([Emp_Name XML].value('/D[3]', 'varchar(50)')) IS NULL
         THEN [Emp_Name XML].value('/D[2]', 'varchar(50)')
                   ELSE [Emp_Name XML].value('/D[3]', 'varchar(50)')
  END AS [Last_Name]
FROM CTE

GO

-----------------------------------------------------------------------------------------------------
Output :
-----------------------------------------------------------------------------------------------------
Notes:

--In the above query, for the XML Tags <D>;</D>, you can use any other letter like <T>;</T>.
--[Emp_Name XML].value('/D[1]' is the First Part of String with Delimiter...
--You can use the delimiter based on your source data.
-----------------------------------------------------------------------------------------------------
You can change/update the delimiter in the Source Table Column as follows :
Update Tbl_EMP
Set Emp_Name = REPLACE(Emp_Name, '_', '&')

Output :
Emp_ID Emp_Name
1            ABC&CDE&DE
2            FGH&HI
3           JKL&LMN&NOP
4            PQR&RS


-----------------------------------------------------------------------------------------------------
Thanks, TAMATAM
-----------------------------------------------------------------------------------------------------





Sunday, 15 October 2017

SQL Server WHILE Loop with BREAK and CONTINUE Keywords

WHILE Loop to Insert Dynamic Date Values into the Table in SQL Server
WHILE Loop statement is used to loop through the body to execute till the Condition evaluates to False.
Since the WHILE condition is evaluated before entering the loop, it is possible that the loop may not execute even once.
The following WHILE Loop example inserts the Dates, Month, Quarter, Year values till '12/31/2020' into the table.

USE TAMATAM
GO

CREATE TABLE Tbl_Dates ([Date_] [Date] NOT NULL PRIMARY KEY CLUSTERED,
      [Week_Number] [Int]  NULL,
      [Week_Day_Name] [Varchar](50) NULL,
      [Month_Number] [Varchar](50) NULL,
      [Month_Name] [Varchar](50) NULL,
      [Qtr_Name] [Varchar](50) NULL,
      [FY_Name] [Varchar](50) NULL,
      [Unique_Id] UniqueIdentifier)
;

-------------------------------------------------------------------------------------------
WHILE Loop :

DECLARE @Val DATE
SET @Val=GETDATE()
PRINT(@Val)
Truncate Table Tbl_Dates

--WHILE Loop Begins here
WHILE @Val <= '12/31/2020'

BEGIN  
INSERT INTO Tbl_Dates
( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
       [Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
       VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
    MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
    'FY' + DateName(YY,@Val),Newid())
  
SELECT @Val=DATEADD(DD,1,@Val)
END
--WHILE Loop Ends here
GO
-------------------------------------------------
Output :
SELECT*FROM Tbl_Dates Order By 1 Desc

SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 Desc
FY_Name
FY2017
FY2018
FY2019
FY2020
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
WHILE Loop with BREAK :
The BREAK keyword is used to Break the Loop when a specific Condition is True, explained below :
--WHILE Loop Begins here
WHILE @Val <= '12/31/2020'

BEGIN  
INSERT INTO Tbl_Dates
( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
       [Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
       VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
       MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
       'FY' + DateName(YY,@Val),Newid())
  
   
SELECT @Val=DATEADD(DD,1,@Val)
  IF YEAR(@Val)='2018'
   BREAK; ----WHILE Loop Exits/Stops here when Year=2018
END
--WHILE Loop Ends here
 SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1
GO

-------------------------------------------------
Output :
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1

FY_Name
FY2017
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

WHILE Loop with CONTINUE:
The CONTINUE keyword will stop the Loop to execute Next Lines of Code after it, and make the loop to go to beginning again.
--WHILE Loop Begins here
WHILE @Val <= '12/31/2020'

BEGIN  
INSERT INTO Tbl_Dates
( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
       [Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
       VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
       MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
       'FY' + DateName(YY,@Val),Newid())
 
 
    SELECT @Val=DATEADD(DD,1,@Val)

CONTINUE --The Loop will go to beginning again, and the next lines(The IF condition section) of Code below in the Loop will not execute.
            IF YEAR(@Val)='2018' --This line will not execute
            BREAK;
 --This line will not execute
END
--WHILE Loop Ends here
 SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1
GO

-------------------------------------------------
Output :
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 Desc

FY_Name
FY2017
FY2018
FY2019
FY2020
-------------------------------------------------------------------------------------------
Thanks, TAMATAM
-------------------------------------------------------------------------------------------


Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts