SQL Server 2012 introduces a new feature called "WITH RESULT SETS".
It makes the stored procedure returning multiple result sets. Lets see an illustration on how this new thing works.
Stored Procedure Code:
CREATE PROCEDURE dbo.usp_MultipleResultSets
AS
BEGIN
SET NOCOUNT ON;
-- Department Result Set
SELECT
[DepartmentID]
,[Name]
,[GroupName]
FROM [HumanResources].[Department]
-- Employee Result Set
SELECT
[BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
FROM [HumanResources].[Employee]
END
It makes the stored procedure returning multiple result sets. Lets see an illustration on how this new thing works.
Stored Procedure Code:
CREATE PROCEDURE dbo.usp_MultipleResultSets
AS
BEGIN
SET NOCOUNT ON;
-- Department Result Set
SELECT
[DepartmentID]
,[Name]
,[GroupName]
FROM [HumanResources].[Department]
-- Employee Result Set
SELECT
[BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
FROM [HumanResources].[Employee]
END
Result Set:
EXEC dbo.usp_MultipleResultSets WITH RESULT SETS
(
(
DepartmentID smallint,
Name dbo.Name,
GroupName dbo.Name
),
(
[BusinessEntityID] [int],
[NationalIDNumber] [nvarchar](15),
[LoginID] [nvarchar](256),
[OrganizationNode] [hierarchyid],
[JobTitle] [nvarchar](50),
[BirthDate] [date],
[MaritalStatus] [nchar](1),
[Gender] [nchar](1),
[HireDate] [date],
[SalariedFlag] [dbo].[Flag],
[VacationHours] [smallint],
[SickLeaveHours] [smallint],
[CurrentFlag] [dbo].[Flag]
)
)
Hope it helps! Happy TSQLing!
