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]

3 comments
Sawsan 29 Aug 2015 at 6:21 am

Michael Hi SteveCan I have the PowerPoint presentation?I would peferr printouts as well coz it make’s it very handy.If it’s okay with you, please email it to me.Thank you so much

TechVsLife 27 Nov 2010 at 2:10 am

NOTE: If I haven’t misread the Denali docs, you CAN’T use this feature to return a different number of columns–you have to return all (this is from help):
“If the number of columns differs from the result set, an error occurs and the batch is aborted.”

Derek Dieter 27 Nov 2010 at 8:17 am

Hi, thank you for this info. I’m sure you’re right as I have not actually tested it out. I’m going to download the CTP now. I am hoping there is a work around possibly using their new replacement procedures for their SET FMT ONLY Statement. I wouldn’t mind trapping the entire result set if I just knew what it was in advance.

Featured Articles

 Site Author

  • Thanks for visiting!