All developers know, that it is very important to encapsulate code in classes and methods to be able to reuse bits and pieces for other purposes than they were originally made for. The same goes for t-sql development, by using stored procedures and functions. But often you need to slightly chance the result set for your needs, to be able to reuse the data from an existing stored procedure for other purposes. Let me demonstrate how you would usually accomplish this using temp tables:

--Create table to hold test data
CREATE TABLE ResultSetTest (
    Id INT IDENTITY (1, 1),
    SomeText VARCHAR(50),
    SomeDate DATETIME2 DEFAULT SYSDATETIME()
)
GO
--Insert test data
INSERT INTO ResultSetTest (SomeText)
SELECT 'This is just some not so random text'
FROM
    sys.objects t1
    CROSS JOIN sys.objects t2
GO
--Create procedure to fetch data
CREATE PROCEDURE GetData
AS
SET NOCOUNT ON
SELECT Id, SomeText, SomeDate
FROM ResultSetTest
GO

Let’s do a very basic performance analysis of this procedure, by examinating the logical reads it performs:

SET STATISTICS IO ON
EXEC GetData
GO

The message output says:

Table 'ResultSetTest'. Scan count 1, logical reads 33, physical reads 0, read-ahead ...

Now let’s try to create a new stored procedure, that reuses the GetData procedure, but transforms the output:

CREATE PROCEDURE MyNewGetData
AS
SET NOCOUNT ON
CREATE TABLE #ResultSetTest (
    id INT,
    SomeText VARCHAR(50),
    SomeDate DATETIME2
)
--Insert result set from sp into temp table
INSERT INTO #ResultSetTest
EXECUTE GetData
--Return the same columns, but cast the SomeDate column
SELECT
    Id,
    SomeText,
    CAST(SomeDate AS DATETIME) AS SomeDateConverted
FROM #ResultSetTest
DROP TABLE #ResultSetTest
GO

We need to create a temp table that matches the result set from the original procedure. Then we insert the data in the temp table, and finally select and convert the data. This is not pretty at all.

If we execute the new procedure, we get the following IO statistics result:

EXEC MyNewGetData
GO

Table 'ResultSetTest'. Scan count 1, logical reads 33, physical reads 0, read-ahead ...
Table 'Worktable'. Scan count 0, logical reads 4257, physical reads 0, read-ahead ...
Table 'Worktable'. Scan count 1, logical reads 9004, physical reads 0, read-ahead ...
Table 'Worktable'. Scan count 1, logical reads 33, physical reads 0, read-ahead ...

That is a total of 13327 logical reads! That is more than 400 times as many reads as the original stored procedure. We must be able to do better than that, so let’s try the new WITH RESULT SETS feature:

ALTER PROCEDURE MyNewGetData
AS
SET NOCOUNT ON
EXECUTE GetData
WITH RESULT SETS
(
    (
    Id INT,
    SomeText VARCHAR(50),
    SomeDateConverted DATETIME -- Notice the change in data type as well as column name
    )
)
GO

EXEC MyNewGetData
GO

Table 'ResultSetTest'. Scan count 1, logical reads 33, physical reads 0, read-ahead ...

This is more like it. Now we only perform the same 33 logical reads as the original, but we managed to rename and convert the output from the procedure as we wanted. And it is not hard to agree, that by using the WITH RESULT SETS we get a way simpler query that gives us exactly what we wanted.

Unfortunately column renaming and type casting is the only transformation possible at the moment. It would be very nice if it was possible to do the following transformations as well:

• Add column with default data
• could be something like: Enabled BIT = 0
• Exclude columns from output
• Reorder columns
• Convert columns by using functions
• could be something like: SomeDateConverted VARCHAR(50) = CONVERT(VARCHAR(50), SomeDate, 112)

and possibly many others.

Some of these transformation has already been requested in this Microsoft connect ticket, but for now we still need to use the temp table approach to do stuff like that.

So please go ahead and vote for the ticket if you would also like these features added.