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:

CREATE PROCEDURE spSalesOverAmt
(
	@Amount money
)
AS
BEGIN

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

END

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

-- 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
	)
);

• with result sets
• EXECUTE WITH RESULT SETS
• Types of execute statement
• denali execute
• sql server execute resultset