After reading my earlier article SQL SERVER – Denali – Executing Stored Procedure with Result Sets, one of the readers asked if this new feature (syntax) support multiple resultset of the stored procedure? Very interesting question indeed as most of the stored procedures that I usually come across have more than one resultset. I quickly look up the syntax online and realize it can be done quite easily. If you are using the earlier method of the temp table inserting the value of the stored procedure by executing, then the it does not support multiple resultset.
This new capability of T-SQL can be really useful.
USE AdventureWorks2008R2
GO
CREATE PROCEDURE mySP1 (@ShiftID INT, @JobCandidateID INT)
AS
-- ResultSet 1
SELECT [ShiftID]
,[Name]
,[StartTime]
,[EndTime]
,[ModifiedDate]
FROM [HumanResources].[Shift]
WHERE [ShiftID] = @ShiftID
-- ResultSet 2
SELECT [JobCandidateID]
,[BusinessEntityID]
,[ModifiedDate]
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = @JobCandidateID
GO
EXEC mySP1 @ShiftID = 2, @JobCandidateID = 5
WITH RESULT SETS
(
(
[ShiftID] TINYINT
,[Name] NVARCHAR(50)
,[StartTime] DATETIME
,[EndTime] DATETIME
,[UpdateDate] DATETIME -- Notice Name Change
),
(
[JobCandidateID] INT
,[BusinessEntityID] INT
,[ModifiedDate] DATETIME
)
);
Here is quick question to all of you – do you see how this feature can help resolve few of the T-SQL issues faced in earlier version? Your opinion really matters.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
