When you execute a stored procedure that returns a result set in SQL Server, the columns returned are defined in the stored procedure.
But did you know that you can redefine those columns?
What I mean is, you can change the names and/or the data type of the columns returned in the result set.
This could save you from having to fiddle with the column headers and data formats in the event you needed to use that result set in another setting.
For example, if a stored procedure returns a datetime2 column, but you only need the date part, you could specify date for that column, and your result set will only include the date part.
And the best part is that you can do it as part of the EXECUTE statement. No need to massage the data after executing the procedure. way to do this is by using the WITH RESULT SETS clause of the EXECUTE statement.
Example
Here’s an example to demonstrate how to use the WITH RESULT SETS clause to change the column names and data types from the result set of a stored procedure.
Raw Results
First, let’s look at the raw results from a stored procedure.
EXEC sp_getCityById @CityId = 1;Result:
+------------+----------------------------+-----------------------------+ | CityName | LatestRecordedPopulation | ValidFrom | |------------+----------------------------+-----------------------------| | Aaronsburg | 613 | 2013-01-01 00:00:00.0000000 | +------------+----------------------------+-----------------------------+
Depending on our requirements, we might wish that the procedure didn’t use such a long column header for the population (LatestRecordedPopulation).
We might also wish that the ValidFrom column didn’t include the time part, as it takes up unnecessary space, and it’s not important for our particular purpose.
We might also want to present the column headers with a space, just to make it look slightly more presentable for whoever it is that we’ll be sending it to.
Redfine the Columns
Now let’s go ahead and use the WITH RESULT SETS clause to redefine the columns.
EXEC sp_getCityById @CityId = 1
WITH RESULT SETS
(
(
[City] nvarchar(50),
[Population] int,
[Valid From] date
)
);Result:
+------------+--------------+--------------+ | City | Population | Valid From | |------------+--------------+--------------| | Aaronsburg | 613 | 2013-01-01 | +------------+--------------+--------------+
So by using the WITH RESULT SETS clause, we were able to change the column names and the data type.
Actually, in this example I changed the data type of the last two columns from bigint to int, and from datetime2(7) to date, respectively.
Analyse the Result Sets
We can use dynamic management views such as sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object to find out the actual data types of each result set.
Here’s an example of using sys.dm_exec_describe_first_result_set_for_object to get the column names and their respective data types returned by the stored procedure.
SELECT
name,
system_type_name,
max_length,
[precision],
scale,
user_type_name
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('sp_getCityById'), 0);Result:
+--------------------------+--------------------+--------------+-------------+---------+------------------+ | name | system_type_name | max_length | precision | scale | user_type_name | |--------------------------+--------------------+--------------+-------------+---------+------------------| | CityName | nvarchar(50) | 100 | 0 | 0 | NULL | | LatestRecordedPopulation | bigint | 8 | 19 | 0 | NULL | | ValidFrom | datetime2(7) | 8 | 27 | 7 | NULL | +--------------------------+--------------------+--------------+-------------+---------+------------------+
So these are the actual column names and data types returned in the result set (without redefining anything).
We can see that the last two columns are bigint and datetime2(7) respectively.
Now let’s use sys.dm_exec_describe_first_result_set to get the metadata for our modified query.
SELECT
name,
system_type_name,
max_length,
[precision],
scale,
user_type_name
FROM sys.dm_exec_describe_first_result_set(
'EXEC sp_getCityById @CityId = 1
WITH RESULT SETS
(
(
[City] nvarchar(50),
[Population] int,
[Valid To] date
)
);',
null,
0
);Result:
+------------+--------------------+--------------+-------------+---------+------------------+ | name | system_type_name | max_length | precision | scale | user_type_name | |------------+--------------------+--------------+-------------+---------+------------------| | City | nvarchar(50) | 100 | 0 | 0 | NULL | | Population | int | 4 | 10 | 0 | NULL | | Valid To | date | 3 | 10 | 0 | NULL | +------------+--------------------+--------------+-------------+---------+------------------+
So we can see that the column names have changed, and the data types of the last two columns have also changed as specified.
Multiple Result Sets
Some stored procedures return multiple result sets. When using WITH RESULT SETS on these procedures, you need to ensure that you include definitions for each result set.
You can’t just redefine some but not the others. If you do that, you’ll get an error.
If you only need to redefine one result set, you need to do them all – even if their definitions remain the same as their original definition.
When doing this, separate each definition with a comma.
Original Result Sets
The following procedure returns three result sets.
EXEC sp_getCityStateCountryByCityId @CityId = 1;Result:
+------------+----------------------------+-----------------------------+ | CityName | LatestRecordedPopulation | ValidFrom | |------------+----------------------------+-----------------------------| | Aaronsburg | 613 | 2013-01-01 00:00:00.0000000 | +------------+----------------------------+-----------------------------+ (1 row affected) +---------------------+---------------------+----------------------------+ | StateProvinceCode | StateProvinceName | LatestRecordedPopulation | |---------------------+---------------------+----------------------------| | PA | Pennsylvania | 13284753 | +---------------------+---------------------+----------------------------+ (1 row affected) +-----------------+---------------+----------------------------+ | IsoAlpha3Code | CountryName | LatestRecordedPopulation | |-----------------+---------------+----------------------------| | USA | United States | 313973000 | +-----------------+---------------+----------------------------+ (1 row affected)
Redefined Result Sets
We can redefine these result sets with the following code.
EXEC sp_getCityStateCountryByCityId @CityId = 1
WITH RESULT SETS
(
(
[City] nvarchar(50),
[Population] int,
[Valid From] date
),
(
[State Code] nvarchar(5),
[State Name] nvarchar(50),
[Population] int
),
(
[Country Code] nvarchar(3),
[Country Name] nvarchar(60),
[Population] int
)
);Result:
+------------+--------------+--------------+ | City | Population | Valid From | |------------+--------------+--------------| | Aaronsburg | 613 | 2013-01-01 | +------------+--------------+--------------+ (1 row affected) +--------------+--------------+--------------+ | State Code | State Name | Population | |--------------+--------------+--------------| | PA | Pennsylvania | 13284753 | +--------------+--------------+--------------+ (1 row affected) +----------------+----------------+--------------+ | Country Code | Country Name | Population | |----------------+----------------+--------------| | USA | United States | 313973000 | +----------------+----------------+--------------+ (1 row affected)
Reducing the Number of Columns Returned by the Stored Procedure
When I first found out about the WITH RESULT SETS clause, I was excited, because I thought it would provide a simple way to reduce the number of columns returned by the stored procedure.
Sadly, that’s not the case.
If you don’t include all of the columns returned by the stored procedure in your WITH RESULT SETS clause, you’ll get an error.
However, all is not lost. See How to Select a Subset of Columns from a Stored Procedure if you want fewer columns than the procedure returns.