Can we change the output column name and their data type being received from a stored procedure during execution?
Yes, SQL Server 2012 introduced “WITH RESULT SETS” option which can be used with EXECUTE command to redefine the metadata of result sets being received from stored procedures. This option can handle single as well as multiple result sets.
“WITH RESULT SETS” option can be useful if we want to reuse an existing stored procedure’s result set(s) with different column name and / or data type. Lets understand this with the help of an example. Below is the stored procedure named “uspGetEmployeeManagers” created in “AdventureWorks2014” datababse. This procedure return the list of managers for a given employee as below:
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 101
Imagine, this stored procedure is being used in many applications throughout organization. Now, in a new … More