Change stored procedure output column names and data types 2


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
Current output columns

Current output columns

Imagine, this stored procedure is being used in many applications throughout organization. Now, in a new requirement, we need the same result set but with a different name “ReportingLevel” for column “RecursionLevel“. We cannot alter this stored procedure, because it might lead to application level errors as it is being used in many applications.

As a workaround, we can copy – paste the code from this stored procedure in a new stored procedure, make the changes accordingly, and use this procedure instead of the existing one. However, in this case, any change in the existing procedure “uspGetEmployeeManagers“, will needs to be replicated in newly created procedure also, which will add some rework.

Another workaround, we can stage the output of this procedure temporarily, and then we can extract all these columns with appropriate aliases in a SELECT statement. In this approach, we have to stage the data temporarily.

Using WITH RESULT SETS to redefine column names and data types of stored procedure’s result set

Using WITH RESULT SETS option, we can redefine the metadata of result set(s) of a stored procedure during execution. We can use the below code to change the column names and data types of result sets(s) of stored procedure without making any change in the existing code:

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 101
WITH RESULT SETS
(
 (
 ReportingLevel INT,
 BusinessEntityID INT,
 FirstName NVARCHAR(50),
 LastName NVARCHAR(50),
 OrganizationNode NVARCHAR(MAX),
 ManagerFirstName NVARCHAR(50),
 ManagerLastName NVARCHAR(50)
 )
)

Here is the output of the stored procedure after redefining the column definitions using WITH RESULT SETS option:

Column name redefined during procedure execution

Column name redefined during procedure execution

Below is the syntax to define column names and data types in case we need to handle multiple result sets:

EXEC ProcedureName @Param = 'Value'
WITH RESULT SETS
(
--Result set 1
(
Column1 DataType,
Column2 DataType,
Column3 DataType
--Define all columns
),
--Result set 2
(
Column1 DataType,
Column2 DataType,
Column3 DataType
--Define all columns
),
--Result set 3
(
Column1 DataType,
Column2 DataType,
Column3 DataType
--Define all columns
)
--And so on
)

WITH RESULT SETS option – Limitations

  1. We cannot redefine a subset of columns – Either all columns of a result set needs to be defined or none of them can be defined using WITH RESULT SETS option.
  2. Cannot redefine a subset of result sets – In case the stored procedure return multiple result sets, either all result sets (with all columns) need to be defined or none of them can be defined.
  3. Cannot change the order of the columns and result sets – We cannot change the sequence of columns and result sets.
  4. Cannot use calculation or type casting – We cannot use calculations or type casting with the columns.

Thank you for the reading. Please like, share, and comment on this post if it is a worth reading for you.

Rate This
[Total: 8    Average: 3.6/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.


Leave a comment

Your email address will not be published. Required fields are marked *

2 thoughts on “Change stored procedure output column names and data types

  • Santosh

    Hey Ranjan,
    Thanks for writing this article. I am facing the same issue while using SSIS. One of the package is using the dynamic query and the OLEDB connector is not able to execute it saying “Dynamic query not supported by OLEDB”. So I thought of using ADO.NET connector instead of OLEDB. It’s a big change keeping in mind the architecture of the package. While going through the articles on the net, I came across this post and it helped me a lot. Once again a big thank you to you… :).