SQL Server Portal

Problem :
Whenever you modify the underlying objects (tables, data types etc) of any non-schema-bound stored procedure, user-defined function, view, trigger mostly due to customization then your object will not address those customization and might give you error or unexpected results. So how to fix this ?

Before proceeding with the solution, let me create a sample table & a table-valued function to demonstrate the solution.

Sample :

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE [dbo].[tbl_Employee]
GO
--Create a table
CREATE TABLE [dbo].[tbl_Employee](
[Employee ID] int ,
[Employee name] [varchar](50) NOT NULL
)
Insert into dbo.[tbl_Employee] VALUES (1,'A1')
Insert into dbo.[tbl_Employee] VALUES (2,'B2')
GO

--DROP FUNCTION fn_Employee
--GO
--Create a table-valued function
CREATE FUNCTION fn_Employee( )
RETURNS TABLE
AS
RETURN
(SELECT * from tbl_Employee)
GO

--Get the data with the help of table-valued function
SELECT * FROM dbo.fn_Employee ()
GO

Solution :
Lets me show you the problem and its solution step by step.

Step 1 :
Lets modify the table by adding a column in it.

USE tempdb
GO
ALTER TABLE tbl_Employee ADD [Address] varchar(100)
GO
SELECT * FROM tbl_Employee
GO
--OUTPUT

Step 2 :
Once you modified the table, lets browse the table-valued function and see if above change (Added a new column in the table)  is reflecting.

USE tempdb
GO
Select * from dbo.fn_Employee ()
GO
--OUTPUT

Step 3 :
Oopss…… If you look at the above result set, you can observe that whatever change (Added a new column in the table) you made in the table, did not reflect in the table-valued function. It means that whenever you modify an underlying object of any non-schema-bound object then the metadata DOES NOT get updated automatically. Lets update it manually with the help of sp_refreshsqlmodule (One of the system stored procedures).

USE tempdb
GO
EXEC sys.sp_refreshsqlmodule 'dbo.fn_Employee'
GO
--OUTPUT

Step 4 :
It seems, table-valued function’s metadata has been updated in the above step. Lets browse the table-valued function to verify whether its metadata has been updated successfully or not.

USE tempdb
GO
Select * from dbo.fn_Employee ()
GO
--OUTPUT

As you can see above the table-valued function’s metadata has been updated successfully.

Conclusion :
The bottom line of this article is, whenever you alter any underlying object (table, datatype) of any non-schema-bound object then DO NOT forget to refresh the metadata of that particular object using sp_refreshsqlmodule.

Read Full Post »