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
        SELECT Title,
               FirstName,
               LastName
        FROM   Person.Person
        WHERE  PersonType LIKE @persontype
    END

Without result set

EXEC Getempdetail '%EM%'
                        






                    
                    
Using Result Set                        

EXEC Getempdetail '%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)
    )


EXEC Getempdetail '%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
        SELECT Title,
               FirstName,
               LastName
        FROM   Person.Person
        WHERE  PersonType LIKE @persontype

        SELECT h.JobTitle,
               h.HireDate
        FROM   Person.Person p
               JOIN HumanResources.Employee h
                   ON h.BusinessEntityID = p.BusinessEntityID
        WHERE  PersonType LIKE @persontype
               AND JobTitle LIKE @designation
    END


EXEC Getempdetailanddesignation '%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.

1 comment:

Post a Comment

Amazon Contextual Product Ads

Popular Posts