Execute with Result Sets new option in Denali

Execute
We all are aware of Execute. Most common use is execute a procedure.
In Denali a new option is added define Metadata for result set using WITH RESULT SETS options.
Lets look into it

[ { EXEC | EXECUTE } ]
    {
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var }
        [ [ @parameter = ] { value
                           | @variable [ OUTPUT ]
                           | [ DEFAULT ]
                           }
        ]
      [ ,…n ]
      [ WITH [ ,…n ] ]
    }
[;]

::=
{
        RECOMPILE
    | { RESULT SETS UNDEFINED }
    | { RESULT SETS NONE }
    | { RESULT SETS ( [,…n ] ) }
}
::=
{
    (
         { column_name
           data_type
         [ COLLATE collation_name ]
         [ NULL | NOT NULL ] }
         [,…n ]
    )
    | AS OBJECT
        [ db_name . [ schema_name ] . | schema_name . ]
        {table_name | view_name | table_valued_function_name }
    | AS TYPE [ schema_name.]table_type_name
    | AS FOR XML
}

As we can see there are 3 options in Result Sets

1. RESULT SETS UNDEFINED
Its a default option when result sets are not provided.
Its return result as it is return by the procedure

2.RESULT SETS NONE
No result is returned. The procedure should not return any result sets else it will give error

3.RESULT SETS

As we can define it multiple ways

We can define column name, data type,collation for the result set while executing the query.
We can give object name whose definition will be used to return result sets
We can get result set as table type and XML

Lets see with Example all one by one

Creating a procedure

CREATE PROC Getempdetail @persontype VARCHAR(50)

AS

    BEGIN

        SELECTTitle,

               FirstName,

               LastName

        FROM   Person.Person

        WHERE  PersonType LIKE@persontype

    END

Without result set

EXECGetempdetail ‘%EM%’

                        

                    

                    

Using Result Set                        

EXECGetempdetail ‘%EM%’

WITH result sets

(

                  (Emp_Title varchar(250),

             Emp_FirstName varchar(250),

             Emp_LastName   varchar(250))

)

Column names are as per the defined result set

 

Using Object

CREATE TABLE test

    (

         Emp_Title     VARCHAR(250),

         Emp_FirstName VARCHAR(250),

         Emp_LastName  VARCHAR(250)

    )

EXECGetempdetail ‘%EM%’

WITH result sets ( AS object[AdventureWorks2008R2].[dbo].[test])

Column names are as per the defined  object

Using Multiple Result Set

CREATE PROC Getempdetailanddesignation @persontype  VARCHAR(50),

                                       @designation VARCHAR(250)

AS

    BEGIN

        SELECTTitle,

               FirstName,

               LastName

        FROM   Person.Person

        WHERE  PersonType LIKE@persontype

        SELECTh.JobTitle,

               h.HireDate

        FROM   Person.Person p

               JOINHumanResources.Employee h

                   ONh.BusinessEntityID =p.BusinessEntityID

        WHERE  PersonType LIKE@persontype

               ANDJobTitle LIKE @designation

    END

EXECGetempdetailanddesignation ‘%EM%’,‘%Manager%’

WITH result sets

(

                  (Emp_Title varchar(250),

             Emp_FirstName varchar(250),

             Emp_LastName   varchar(250))

             ,

             (JobTitle varchar(250),

             HireDate Date)

)

Multiple result sets are defined by comma separator.