SQL Server Denali – EXECUTE Statement WITH RESULT SETS

When I had originally written this article, I was very excited that this would be an answer to a long-standing wish that I have hope SQL Server would support. I wished I could call a stored procedure without knowing what was in the result set and dump it to a table (or temp table). Currently in order to trap the result set from executing a procedure we need to perform work arounds – all because we do not dynamically know the columns and data types the procedure is returning. If there was a way SQL could facilitate this without a work-around, it would take
SQL to a new level of abstraction and modularity.

The WITH RESULT SETS feature does allow us to change the column names and datatypes of the result set a stored procedure returns, however it does not allow us to define a subset of columns to return. Therefore, if a new column is added to the result set of a procedure that is called using WITH RESULT SETS, an error will occur.

The SQL Server Denali CTP can be downloaded here.

Let’s take a look at this new feature, first we’ll create a sample stored procedure:
[cc lang=”sql”]
CREATE PROCEDURE spSalesOverAmt
(
@Amount money
)
AS
BEGIN

SELECT
SalesOrderID,
OrderQty,
UnitPrice
FROM Sales.SalesOrderDetail
WHERE UnitPrice > @Amount

END
[/cc]

Now let’s change the column names and the data types:

[cc lang=”sql”]
— Execute the procedure with a parameter
EXEC spSalesOverAmt @Amount = 1200
WITH RESULT SETS
(
(
— Specify the columns we want and
— the data types we want them to be
SalesOrderID smallint,
Units smallint,
UnitAmount float
)
);
[/cc]