Home » Change the column name and data type of result set of stored procedure

Change the column name and data type of result set of stored procedure

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




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.