SSIS : WITH RESULT SETS in Denali

In a blog of Derek Dieter i've read something about executing stored procedures with result sets. As it seems now to me, the WITH RESULT SETS clause of the EXEC statement makes it possible to specify explicitly the result set of a stored procedure (and not change it with casting). At this moment the following websites seems to have some interesting information about this option:

• Microsoft SQL Server forum.
• James Rowland-Jones.
• MSDN.

The EXEC statement is enhanced with the following clauses:

• RESULT SETS UNDEFINED 
• RESULT SETS NONE
• RESULT SETS ( <result_sets_definition> [,...n ] )

And the <result_set_definition> does have the following possibilities specifying the result:

• Datatype.
• Length.
• NOT NULL| NULL 
• Collation.

Although it's possible to do some casting of datatypes of fields, it's only possible when the data fits into this sort of a cast. Below you can see some examples i experimented with.

First let's create a stored procedure:

CREATE PROCEDURE spGetPerson
AS
BEGIN
    SELECT FirstName, MiddleName, LastName FROM Person.Person
END 

and execute it :

EXEC spGetPerson
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50),
        NameLast NVarchar(50)
    )
);


Execute this will give an error:

EXEC spGetPerson
WITH RESULT SETS NONE
GO

Msg 11535, Level 16, State 1, Procedure spGetPerson, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.


And executing this is ok :


EXEC spGetPerson
WITH RESULT SETS UNDEFINED
GO

You can also use this for executing this :

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50) NOT NULL,
        NameLast NVarchar(50)
    )
);


And this will give an error:

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameLast NVarchar(50)
    )
);

Msg 11537, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.


And  the following script shows all the possibilities you have with RESULT SETS (except multiple Result sets):

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50) NOT NULL,
        NameMiddle nVarchar(50) NULL,
        NameLast NVarchar(50) COLLATE Latin1_General_100_CI_AS
    )
);


And the final example shows multiple result sets:

--Suppose you have two resultsets:
CREATE PROC Production.ProductList @ProdName nvarchar(50)
AS
-- First result set
SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE Name LIKE @ProdName;
-- Second result set
SELECT Name, COUNT(S.ProductID) AS NumberOfOrders
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS S
        ON P.ProductID  = S.ProductID
    WHERE Name LIKE @ProdName
    GROUP BY Name;
GO

-- Execute the procedure
EXEC Production.ProductList '%tire%'
WITH RESULT SETS
(
    (ProductID int,   -- first result set definition starts here
    Name Name,
    ListPrice money)
    ,                 -- comma separates result set definitions
    (Name Name,       -- second result set definition starts here
    NumberOfOrders int)
);


Conclusion:
It sounded as a promising feature to me but i'm a bit disappointed. There are only four options available and i realize that it has some potential. But selecting some columns is not possible, and many more things.

Greetz,
Hennie