With Result Sets
The With Result Sets feature of Denali allow us to change the column names and data types of the result set that a stored procedure returns.
Before going to explore that,let us observe how in earlier versions of Sql Server (pre-Denali), we used to get the values from an executing stored procedure.
For this demonstration, we will use the below table (tbl_Test), having 3 columns as the example.
Now let us populate some records (say 1000) to the table by using the below script
Collapse
-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Test' AND type = 'U')
DROP TABLE tbl_Test
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[tbl_Test](
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[PhoneNumber] [int] NOT NULL
) ON [PRIMARY]
GO
--Populate the Cte with some records
;With Cte(Id,Name,PhoneNo) As (
Select
Id = 1
,Name='Name' + CAST( 1 As Varchar(50))
, PhoneNo=12345678
Union All
Select
Id+1
,Name= 'Name' + CAST( Id+1 As Varchar(50))
, PhoneNo+1
From Cte
Where Id <1000
)
--Insert the records into the table
Insert Into dbo.tbl_test
Select * From Cte
Option( Maxrecursion 0)
--Display the records
Select *
From tbl_Test
Running the script will bring the below record set (partial)
Collapse
Id Name PhoneNumber
1 Name1 12345678
2 Name2 12345679
3 Name3 12345680
4 Name4 12345681
5 Name5 12345682
Let us write a stored procedure for fetching the result from the table tbl_Test.
Collapse
CREATE PROCEDURE dbo.Usp_FetchRecords
AS
BEGIN
Select
Id
,Name
,PhoneNumber
From dbo.tbl_Test
END
In order to get the result set from an executing stored procedure, there are various approaches available .We will,however, look into one of the approaches
Temporary Table Approach
Collapse
--If the #Temp object exists in the tempdb, then drop it
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
Drop Table #Temp
END
--Create a temporary table
CREATE TABLE #Temp
(
Id int,
EmpName Varchar(50),
PhoneNo int
)
--Insert records into the Temporary table from the executed stored proc
INSERT INTO #Temp
(
Id
,EmpName
,PhoneNo
)
EXEC dbo.Usp_FetchRecords
--Display the records inserted into the temporary table
Select * from #Temp
The above approach is fine if we know in advance the columns and their data types being returned by the stored procedure.
Disadvantages of the pre-Denali approaches are
- None of the approach was straight forward. In the sense, we need the help of a temporary table or variable, dispose that once the operation is over;else that will consume unnecessary database space
- Process was lengthy
- In the case of Open Row Set or Open query, we need to turn on the ‘Ad Hoc Distributed Queries’ feature and then to proceed.
- In the case of Temporary table or Table variable approach, we need to know in advance what is the data type of the column
Denali’s (Sql Server 2011) With Result Set Approach
Denali’s With Result Set has overcome the above drawbacks. Let us see how. Let us execute the below query(for single result set)
Collapse
EXEC Usp_FetchRecords
WITH RESULT SETS
(
( [Emp Id] int,
[Emp Name] varchar(50),
[Phone Number] varchar(50)
)
)
The output being (partial output)
Collapse
Emp Id Emp Name Phone Number
1 Name1 12345678
2 Name2 12345679
3 Name3 12345680
4 Name4 12345681
5 Name5 12345682
The general syntax of With Result Set will be
Collapse
WITH RESULT SETS
(
(
Column Name1 DataType [Size]
, Column Name2 DataType [Size]
, . . . . . . . . . . . .
, . . . . . . . . . . . . . .
, Column Name-n DataType [Size]
)
,
(
Column Name1 DataType [Size]
, Column Name2 DataType [Size]
, . . . . . . . . . . . .
, . . . . . . . . . . . . . .
, Column Name-n DataType [Size]
)
. . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . .
,
(
Column Name1 DataType [Size]
, Column Name2 DataType [Size]
, . . . . . . . . . . . .
, . . . . . . . . . . . . . .
, Column Name-n DataType [Size]
)
)
Henceforth, we can make out that, irrespective of the column name(s) returned in the result set, we can change the column names and it’s data Types as long as the data Type conversion is compatible with the original result set(i.e. the data types defined in the table schema). Else the database engine will report error.
e.g. in the below example we have changed the Name columns data type(originally as varchar(50)) to int.
Collapse
EXEC Usp_FetchRecords
WITH RESULT SETS
(
( [Emp Id] int,
[Emp Name] int, -- Changed to int data type
[Phone Number] varchar(50)
)
)
Upon execution, we will receive the below error message
Msg 8114, Level 16, State 2, Procedure Usp_FetchRecords, Line 5 Error converting data type varchar to int.
Whereas changing the same to Text (for example) data type works fine.
The above query was made for demonstration purpose of transforming a single Result Set with Execute’s With Result Set. However, it can be extended to transform for multiple result set. Let us see how.
Consider the below stored procedure, which returns two different record sets.
Collapse
CREATE PROCEDURE [dbo].[Usp_ModifiedFetchRecords]
AS
BEGIN
Select
Id
,Name
,PhoneNumber
From dbo.tbl_Test;
Select
Id
,Name
From dbo.tbl_Test
Where PhoneNumber % 2 = 0
END
The second select statement generates the records for those Names who are having an even digit phone number.
The partial output is as under after executing the stored procedure
Now , let us execute the below query
Collapse
EXEC Usp_ModifiedFetchRecords
WITH RESULT SETS
(
( [Emp Id From First Result Set] int,
[Emp Name From First Result Set] varchar(50),
[Phone Number From First Result Set] varchar(50)
) ,
( [Emp Id From Second Result Set] int,
[Emp Name From Second Result Set] varchar(50)
)
)
The partial output being
However, since the stored procedure is returning two record set(in this case), if we try to obtain only one record set in the With Result Sets clause , the engine will report the below error
Msg 11535, Level 16, State 1, Procedure Usp_ModifiedFetchRecords, Line 11 EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.
As can be seen that the With Result Set clause of the Execute command basically does transformation on the result set of the stored procedure. It has eliminated the drawbacks of the aforesaid procedures for sharing data of the stored procedure.
How can get the values from the With Result Set
We may sometime need to get the value from the With Result Set clause. In such a case we can go ahead with a Temporary table or Table variable approach.
Here we will look into the Table Variable Approach
Collapse
Declare @tblStoreWithResultSetsData Table
([Employee Id] int
, [Employee Name] varchar(50)
,[Emp Phone No] int)
insert into @tblStoreWithResultSetsData
EXEC Usp_FetchRecords
WITH RESULT SETS
(
( [Emp Id] int,
[Emp Name] varchar(6), -- as a proof of concept,
-- change the dataType size to 6.
-- Records will be truncated
[Phone Number] varchar(50)
)
)
Select * From @tblStoreWithResultSetsData
The output being as expected (last 10 records shown)
Applicability
- Data conversion will become simpler in SSIS as described well in this article
- Changing the data type without changing the schema. Suppose a dotnet application is expecting a Boolean and the underlying schema was designed as of type int for that column.Ideally we do a conversion at runtime as Case When Then 1 Else 0. Instead of that, we can directly change the data type to bit.
- Another example can be say the dotnet application is expecting a int but the column type is float.
- Another usage may be say the schema has been changed and the DAL layer is not aware of this. May be the same stored procedure is called from multiple places. In such a scenario, we can just change the column names at runtime in the With Result Set so that the table schema as well as the DAL logic will be un touched.
DrawBacks
We cannot return selected columns. The number of columns has to be same as that of the result set. For example, if we write something as under
Collapse
EXEC Usp_FetchRecords
WITH RESULT SETS
(
( [Emp Id] int,
[Phone Number] varchar(50)
)
)
The engine will report the below error
Msg 11537, Level 16, State 1, Procedure Usp_FetchRecords, Line 5 EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.