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:
WHERE UnitPrice > @Amount
Now let’s change the column names and the data types:
EXEC spSalesOverAmt @Amount = 1200
WITH RESULT SETS
-- Specify the columns we want and
-- the data types we want them to be