SQL Server 2011 - EXECUTE Proc WITH RESULT SETS

Another new feature of Denali, is the ability to enforce that the results of a stored procedure meet your client app's data contract, without having to modify the stored procedure. This is pretty useful, as it will allow you to have one stored procedure, servicing multiple apps, without the need for custom formatting at the front-end.
It will allow you to perform implicit conversions, and even change the local of a column, with the EXECUTE statement. Cool, eh?

I created the following Proc in the AdventureWorks2008 Database...

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

...Then executed the procedure with the following statement...

...This produced the following results...

...As you can see, it has CAST the modified date to a DATE column, and the Firstname column has also had it's collation changed.

I then altered the procedure as below...

...And executed it again with the following code...

...And it failed with the following, (slightly missleading) error...

Msg 8114, Level 16, State 2, Procedure ResultSetsDemo, Line 12
Error converting data type nvarchar to nvarchar.

..In actual fact, the failure is because there are NULL values in MiddleName, and I have specified NOT NULL. (I though it would be kind of nice if it implicitly filtered out the NULLs, but I can see why it doesn't!).

Running the EXECUTE statement again, with the NOT NULL constraint removed, produces the expected results...

EXEC ResultSetsDemo
WITH RESULT SETS
(
     (
     Firstname  VARCHAR(50) COLLATE Albanian_BIN2,
     MiddleName  CHAR(5),
     LastName  VARCHAR(50),
     ModifiedDate DATE
     ),
     (
     FirstName VARCHAR(50),
     MiddleName CHAR(50),
     LastName VARCHAR(50)
     )
)

EXEC ResultSetsDemo
WITH RESULT SETS
(
     (
     Firstname  VARCHAR(50) COLLATE Albanian_BIN2,
     MiddleName  CHAR(5),
     LastName  VARCHAR(50),
     ModifiedDate DATE
     ),
     (
     FirstName VARCHAR(50),
     MiddleName CHAR(50) NOT NULL,
     LastName VARCHAR(50)
     )
)

ALTER PROCEDURE ResultSetsDemo
AS
BEGIN
     SELECT  FirstName
     ,       MiddleName
     ,       LastName
     ,       ModifiedDate
     FROM Person.Person

     SELECT FirstName
     ,      MiddleName
     ,      LastName
     FROM Person.Person
END

EXEC ResultSetsDemo
WITH RESULT SETS
(
    (
     Firstname VARCHAR(50) COLLATE Albanian_BIN2,
     MiddleName CHAR(5),
     LastName VARCHAR(50),
     ModifiedDate DATE
     )
)

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom