SQL Server 2012 has released a new feature WITH RESULT SETS, which allows column names and their data types to be changed in the result set of a stored procedure.
If you need to change the column names and their data types after executing a stored then you can execute the stored procedure using WITH RESULT SETS feature.
It is very helpful when for any particular requirement you need to display some other name of columns or a data type of the result set of a stored procedure, Previously to release of this feature in such case you need to make changes for column name or data type to all the reference within a stored procedure which can be a challenging for you sometime, if you have long scripts in stored procedure.
But with the release of WITH RESULT SETS feature in SQL Server 2012 it has become quite easy.
Lets see the use of WITH RESULT SETS feature in SQL Server.
To demonstrate this first we will Create a table and Insert some records into table, then create a Stored procedure.
CREATE TABLE dbo.CategoryMaster
(ID INT IDENTITY(1,1),
Category VARCHAR(100),
Subcategory VARCHAR(100),
Amount NUMERIC(9,2))
GO
INSERT INTO dbo.CategoryMaster
(Category, Subcategory, Amount)
VALUES
('A', 'A1', 100),('A', 'A2', 150),
('A', 'A3', 45),('A', 'A4', 300),
('B', 'B1', 150),('B', 'B2', 50),
('B', 'B3', 600),('B', 'B4', 100),
('B', 'B5', 150),('C', 'C1', 180),
('C', 'C2', 580),('C', 'C3', 700),
('D', 'D1', 80),('D', 'D2', 380),
('D', 'D3', 150),('D', 'D4', 100),
('E', 'E1', 120),('E', 'E2', 140),
('F', 'F1', 90),('F', 'F2', 60)
As you can see, now we have a table named CategoryMaster as shown below. SELECT * FROM dbo.CategoryMaster
Lets create a stored procedure, which takes one parameter for category and based on parameter value it returns a details for that category from CategoryMaster table.
Lets execute the stored procedure and see the records for category A.
EXEC USP_getCategoryDetails 'A'
As you can see it returns the records related to category A, so far it is fine.
Assume that you want to change some columns name and data type within the result set of stored procedure then you can use WITH RESULT SETS feature rather than making a changes to all the references of column name and their datatype within stored procedure.
Lets say you want to display ID column as ItemId, Category column as ParentItem, and Subcateogry column as ChildItem. Also want to change the data type for Amount column from Numeric to INT.
Lets execute the stored procedure using WITH RESULT SETS.
EXEC USP_getCategoryDetails 'A' WITH RESULT SETS ( ( ItemId INT, ParentItem VARCHAR(100), ChildItem VARCHAR(100), Amount INT ) )
[ Also read: How to get the dark theme for SQL Server Management studio]
You can see, the Id column is changed to ItemId, Category column is changed to ParentItem, Subcategory column is changed to ChildItem, while the data type of Amount column is changed to INT from Numeric.
Suppose in case when your stored procedure returning multiple result set.
Lets modify the above stored procedure for returning a multiple result set, lets return the max, and min amount for category.
ALTER PROC USP_getCategoryDetails @CategoryName VARCHAR(5) AS BEGIN SET NOCOUNT ON; SELECT ID, Category, SubCategory, Amount FROM dbo.CategoryMaster WHERE Category =@CategoryName; SELECT Category, MAX(Amount) AS Max_Amount, MIN(Amount) AS Min_Amount FROM dbo.CategoryMaster WHERE Category =@CategoryName GROUP BY Category ; SET NOCOUNT OFF; END
Lets execute the stored procedure to see the records for category ‘A’.
EXEC USP_getCategoryDetails 'A'
You can see, now it returns two result sets.
Lets change the column names or data types in such case wherein stored procedure returns multiple result sets.
For the first result set, again we will change the columns same as did earlier such as ID will be changed to ItemId, Cateogry will be changed to ParentItem, Subcategory will be changed to ChildItem and the data type of Amount column will be changed to interger.
For the second result set, Category will be changed to ParentItem, Max_Amount to MaximumAmount, Min_Amount to MinimumAmount and Data type of Amount column is changed to INT.
Lets execute the stored procedure using WITH RESULT SETS, and change the name of columns or data type within multiples result set.
You can see, it change the columns name and data type of multiple result sets as specified.
Also Read..
Deal with SQL Server Verbose Truncation Warnings
Display currency symbol in SQL Sever
Displaying Emoji in SQL Server
Create a custom shortcuts in SQL Server
Displaying line numbers in Query Editor Window
