What is WITH RESULT SETS in SQL Server 2012 ?
WITH RESULT SETS provides flexibility in getting output of stored procedure in two ways :
1.) Column names returned as the result of stored procedure execution can be changed
2.) Data type of columns returned as the result of stored procedure execution can be changed
WITH RESULT SETS can be used only at the time of executing stored procedure.
Examples for understanding WITH RESULT SETS :
--Example 2 - Only compatible data type conversion is possible
-- In below example - column NationalID data type NVARCHAR(20) is converted to INT

--Incompatible data type conversion is not possible
--In below example - column Sex data type NCHAR(10) is converted to INT
Msg 8114, Level 16, State 2, Procedure With_Resultset, Line 8
Error converting data type nchar(1) to int.
-- Example - 3 : Cannot exclude columns returned by stored procedure while defining WITH RESULT SETS
--In the below example DateOfJoining column is omitted while defining WITH RESULT SETS
--Note : also cannot include extra columns while defining WITH RESULT SETS
Msg 11537, Level 16, State 1, Procedure With_Resultset, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 4 column(s) for result set number 1, but the statement sent 5 column(s) at run time.
--Example - 4:
-- WITH RESULT SETS NONE - If any results are returned by stored procedure ,then batch will be aborted.
Msg 11535, Level 16, State 1, Procedure With_Resultset, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.
--Example - 5 :
--WITH RESULT SETS UNDEFINED - stored procedure will execute without error irrespective of any results are returned or no results are returned by stored procedure.

--Example - 6:

--Example 7 : -- Inserting into temp table output of stored procedure executed using WITH RESULT SETS is not possible
--without (WITH RESULT SETS)
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'SETS'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#TMP2'.
Restrictions in using WITH RESULT SETS
Number of columns defined inside WITH RESULT SETS should always match the number of columns returned as the result of stored procedure execution
Data type of columns returned as the result of stored procedure execution can be changed,but incompatible data type conversion is not possible.
Below method is not possible using WITH RESULT SETS
WITH RESULT SETS provides flexibility in getting output of stored procedure in two ways :
1.) Column names returned as the result of stored procedure execution can be changed
2.) Data type of columns returned as the result of stored procedure execution can be changed
WITH RESULT SETS can be used only at the time of executing stored procedure.
Examples for understanding WITH RESULT SETS :
USE AdventureWorks2012 --Example 1 CREATE PROCEDURE With_Resultset --Procedure to get top 5 married employee details AS BEGIN SELECT TOP 5 BusinessEntityID, NationalIDNumber, JobTitle, Gender, HireDate FROM [HumanResources].[Employee] WHERE MaritalStatus = 'M' END -- actual column names returned by stored procedure are changed using WITH RESULT SETS EXECUTE With_Resultset WITH RESULT SETS ( ( BusinessID INT, NationalID NVARCHAR(20), Designation NVARCHAR(100), Sex NCHAR(10), DateOfJoining DATE ) ) GO
--Example 2 - Only compatible data type conversion is possible
-- In below example - column NationalID data type NVARCHAR(20) is converted to INT
EXECUTE With_Resultset WITH RESULT SETS ( ( BusinessID INT, NationalID INT, Designation NVARCHAR(100), Sex NCHAR(10), DateOfJoining DATE ) ) GO
--Incompatible data type conversion is not possible
--In below example - column Sex data type NCHAR(10) is converted to INT
EXECUTE With_Resultset WITH RESULT SETS ( ( BusinessID INT, NationalID INT, Designation NVARCHAR(100), Sex INT, DateOfJoining DATE ) ) GO
Msg 8114, Level 16, State 2, Procedure With_Resultset, Line 8
Error converting data type nchar(1) to int.
-- Example - 3 : Cannot exclude columns returned by stored procedure while defining WITH RESULT SETS
--In the below example DateOfJoining column is omitted while defining WITH RESULT SETS
--Note : also cannot include extra columns while defining WITH RESULT SETS
EXECUTE With_Resultset WITH RESULT SETS ( ( BusinessID INT, NationalID INT, Designation NVARCHAR(100), Sex NCHAR ) ) GO
Msg 11537, Level 16, State 1, Procedure With_Resultset, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 4 column(s) for result set number 1, but the statement sent 5 column(s) at run time.
--Example - 4:
-- WITH RESULT SETS NONE - If any results are returned by stored procedure ,then batch will be aborted.
EXECUTE With_Resultset WITH RESULT SETS NONE
Msg 11535, Level 16, State 1, Procedure With_Resultset, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.
--Example - 5 :
--WITH RESULT SETS UNDEFINED - stored procedure will execute without error irrespective of any results are returned or no results are returned by stored procedure.
EXECUTE With_Resultset WITH RESULT SETS UNDEFINED
--Example - 6:
CREATE PROCEDURE With_Multiple_Resultset --Procedure to get top 5 single & married employee details AS BEGIN SELECT TOP 5 BusinessEntityID, NationalIDNumber, JobTitle, Gender, HireDate FROM [HumanResources].[Employee] WHERE MaritalStatus = 'M' SELECT TOP 5 BusinessEntityID, NationalIDNumber, JobTitle, Gender, HireDate FROM [HumanResources].[Employee] WHERE MaritalStatus = 'S' END --Example for multiple result sets EXECUTE With_Multiple_Resultset WITH RESULT SETS ( ( BusinessID INT, NationalID NVARCHAR(20), Designation NVARCHAR(100), Sex NCHAR(10), DateOfJoining DATE ), ( BusinessID INT, NationalID NVARCHAR(20), Designation NVARCHAR(100), Sex NCHAR(10), DateOfJoining DATE ) ) GO
--Example 7 : -- Inserting into temp table output of stored procedure executed using WITH RESULT SETS is not possible
--without (WITH RESULT SETS)
CREATE TABLE #TMP1 ( BusinessID INT, NationalID NVARCHAR(20), Designation NVARCHAR(100), Sex NCHAR(10), DateOfJoining DATE ) INSERT INTO #TMP1 EXECUTE With_Resultset GO SELECT * FROM #TMP1
--with (WITH RESULT SETS) CREATE TABLE #TMP2 ( BusinessID INT, NationalID NVARCHAR(20), Designation NVARCHAR(100), Sex NCHAR(10), DateOfJoining DATE ) INSERT INTO #TMP2 EXECUTE With_Resultset WITH RESULT SETS ( ( BusinessID INT, NationalID NVARCHAR(20), Designation NVARCHAR(100), Sex NCHAR(10), DateOfJoining DATE ) ) GO SELECT * FROM #TMP2
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'SETS'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#TMP2'.
Restrictions in using WITH RESULT SETS
Number of columns defined inside WITH RESULT SETS should always match the number of columns returned as the result of stored procedure execution
Data type of columns returned as the result of stored procedure execution can be changed,but incompatible data type conversion is not possible.
Below method is not possible using WITH RESULT SETS
CREATE TABLE #TMP ...
INSERT INTO #TMP
EXECUTE Procedure
WITH RESULT SETS
(
)
SELECT * FROM #TMP INSERT INTO #TMP
EXECUTE Procedure
WITH RESULT SETS
(
)
No comments:
Post a Comment