MS SQL 2011 - Modification of the returned data set
In the original sound and in life, this opportunity sounds like With Result Set . This piece allows you to change the names and data types in the data set returned by the stored procedure.
Before we dive into the details of using this feature, let's look at how previous versions of SQL servers dealt with the data returned by the stored procedure. What opportunities they provided to work with the result.
To demonstrate the work, we will use the tbl_Test table consisting of 3 columns as an example.
')
Now write some information there. Let it be 1000 entries:
Running the script above will output approximately the following data set (part)
Now we will write a procedure that will output the query data to the tbl_Test table .
There are several ways to get a final set of data on the results of the stored procedure. Some of them are described and discussed in an article by Erland Sommarskog in his article . We will use one of the approaches based on temporary tables.
The approach described above works perfectly well if we know in advance which columns and what type of output is required.
The disadvantages of this and similar approaches:
The new version allows you to get rid of these shortcomings and now we will see exactly how.
The output will be:
The general syntax for using With Result Set is :
In this way, you can arbitrarily change the names of the columns in the resulting dataset. You can change the data type within the limits that are allowed for implicit type conversion. Otherwise, the database will generate an error.
Thus, in the example below, the base will generate an error about the wrong type conversion. We are trying to return the int type while the field is declared as varchar (50) .
During script execution, we get the following error:
Msg 8114, Level 16, State 2, Procedure Usp_FetchRecords, Line 5 Error converting data type varchar to int.
The query that was demonstrated above is designed to convert a single result dataset using the With Result Set. However, this technique, as can be seen from the general syntax, can be extended to several result sets. Now there will be an example of how to do this.
Imagine that we have a stored procedure that returns two data sets.
The second select returns subscribers with even phone numbers. A sample run might be (part of the result):
Now we will try to apply With Result Set to get a more digestible result without changing the stored procedure itself.
The result of the execution will now be in the spirit of:
In this case, the stored procedure returns two result data sets, but if we try to process only one of them in the With Result Set, we get an error from the SQL engine.
Msg 11535, Level 16, State 1, Procedure Usp_ModifiedFetchRecords, Line 11, Result SETS;
Sometimes it may be necessary to additionally process the value obtained with the With Result Set. In this case, you can use temporary tables or table type variables.
Consider an approach using table type variables.
The result will be expected, the name of the employee is reduced to 6 characters. This can be seen in the following screenshot (last 10 entries)
You can not make a selective change of columns in the final data set. For example, the following script will cause an error on execution:
Since the procedure returns a set of three columns. The error will be as follows:
Msg 11537, State 16, State 1, Procedure Usp_FetchRecords, Line 5 RESET SETS 2 column (s) at run time.
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
Before we dive into the details of using this feature, let's look at how previous versions of SQL servers dealt with the data returned by the stored procedure. What opportunities they provided to work with the result.
To demonstrate the work, we will use the tbl_Test table consisting of 3 columns as an example.
')
-- , 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 TABLE [dbo].[tbl_Test]( [Id] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [PhoneNumber] [int] NOT NULL ) ON [PRIMARY] GO Now write some information there. Let it be 1000 entries:
-- CTE ;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 Into dbo.tbl_test Select * From Cte Option( Maxrecursion 0) -- Select * From tbl_Test Running the script above will output approximately the following data set (part)
Id Name PhoneNumber 1 Name1 12345678 2 Name2 12345679 3 Name3 12345680 4 Name4 12345681 5 Name5 12345682 ...
Now we will write a procedure that will output the query data to the tbl_Test table .
CREATE PROCEDURE dbo.Usp_FetchRecords AS BEGIN Select Id ,Name ,PhoneNumber From dbo.tbl_Test END There are several ways to get a final set of data on the results of the stored procedure. Some of them are described and discussed in an article by Erland Sommarskog in his article . We will use one of the approaches based on temporary tables.
Using temporary tables
-- #Temp tempdb, IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN Drop Table #Temp END -- CREATE TABLE #Temp ( Id int ,EmpName Varchar(50) ,PhoneNo int ) -- , INSERT INTO #Temp ( Id ,EmpName ,PhoneNo ) EXEC dbo.Usp_FetchRecords -- Select * from #Temp The approach described above works perfectly well if we know in advance which columns and what type of output is required.
The disadvantages of this and similar approaches:
- None of the approaches offer a direct solution to the problem. In any case, support from temporary tables or variables is required. Consumes space in the database, under the temporary table.
- Query time increases
- When Open Raw Set or Open query queries are required, you must explicitly enable the Ad Hoc Distributed Queries function and then begin the work.
- In the case of the use of temporary tables or table-type variables, you need to know in advance the structure of the response procedure.
New approach MS SQL 2011
The new version allows you to get rid of these shortcomings and now we will see exactly how.
EXEC Usp_FetchRecords WITH RESULT SETS( ( [Emp Id] int ,[Emp Name] varchar(50) ,[Phone Number] varchar(50) ) ) The output will be:
Emp Id Emp Name Phone Number 1 Name1 12345678 < 2 Name2 12345679 < 3 Name3 12345680 < 4 Name4 12345681 < 5 Name5 12345682 < ...
The general syntax for using With Result Set is :
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] ) ) In this way, you can arbitrarily change the names of the columns in the resulting dataset. You can change the data type within the limits that are allowed for implicit type conversion. Otherwise, the database will generate an error.
Thus, in the example below, the base will generate an error about the wrong type conversion. We are trying to return the int type while the field is declared as varchar (50) .
EXEC Usp_FetchRecords WITH RESULT SETS( ( [Emp Id] int ,[Emp Name] int -- int ,[Phone Number] varchar(50) ) ) During script execution, we get the following error:
Msg 8114, Level 16, State 2, Procedure Usp_FetchRecords, Line 5 Error converting data type varchar to int.
The query that was demonstrated above is designed to convert a single result dataset using the With Result Set. However, this technique, as can be seen from the general syntax, can be extended to several result sets. Now there will be an example of how to do this.
Imagine that we have a stored procedure that returns two data sets.
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 returns subscribers with even phone numbers. A sample run might be (part of the result):
Now we will try to apply With Result Set to get a more digestible result without changing the stored procedure itself.
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 result of the execution will now be in the spirit of:
In this case, the stored procedure returns two result data sets, but if we try to process only one of them in the With Result Set, we get an error from the SQL engine.
Msg 11535, Level 16, State 1, Procedure Usp_ModifiedFetchRecords, Line 11, Result SETS;
How to get data from With Result Set
Sometimes it may be necessary to additionally process the value obtained with the With Result Set. In this case, you can use temporary tables or table type variables.
Consider an approach using table type variables.
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) -- , -- 6. -- ,[Phone Number] varchar(50) ) ) Select * From @tblStoreWithResultSetsData The result will be expected, the name of the employee is reduced to 6 characters. This can be seen in the following screenshot (last 10 entries)
Possible use
- Data conversion in SSIS packages will be easier ; for an additional description, see the article here .
- Change data types without changing the schema . Imagine that a .Net application expects a value of a boolean type, and a value in the table is encoded with an int or char (1) type. In general, you can apply value conversion with the Case When Then Else construct. But it’s easier and more pleasant to immediately change the data type to bit (in the case of int).
- Another example of the application With Result Set, when the .Net program is waiting for an int, and the column in the database is of type float.
- Possible immunity of DAL to schema changes. This refers to positive immunity when using the With Result Set set the column names for the resulting dataset. Then it will not matter how the names change in the database itself. A kind of VIEW analogue for stored procedures.
Restrictions
You can not make a selective change of columns in the final data set. For example, the following script will cause an error on execution:
EXEC Usp_FetchRecords WITH RESULT SETS( ( [Emp Id] int ,[Phone Number] varchar(50) ) ) Since the procedure returns a set of three columns. The error will be as follows:
Msg 11537, State 16, State 1, Procedure Usp_FetchRecords, Line 5 RESET SETS 2 column (s) at run time.
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
Source: https://habr.com/ru/post/123573/
All Articles