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

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

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.

Blog Stats

Follow Blog via Email

Disclaimer

This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.