How to redefine the returning result sets of a stored procedure?

This feature was introduced in SQL Server 2012. Using With Result Sets option with Execute Command we can change the column name and data type of returning result set.

Ex:

Use TempDB 
Go 
Create Table Table1(ID int, Name varchar(10), DOB datetime)
   Go 
Insert Into Table1 values (1,’A’,’1980-04-10 06:09:00.100′),(1,’B’,’1982-03-08 03:42:41.002′)
   Go 
Create Procedure Procedure1 
As 
Begin 
Select ID, Name, DOB 
From Table1 
End 
Go
Exec Procedure1 
Go 
Exec Procedure1 With Result Sets ((MyID bigint Not Null, MyName varchar(7) Not Null, DateOfBirth Date Not Null)); 
Go 
Drop Table Table1 
Drop Procedure Procedure1

Output: 
(2 row(s) affected) 
ID          Name       DOB 
———– ———- ———————– 
1           A          1980-04-10 06:09:00.100 
1           B          1982-03-08 03:42:41.003

(2 row(s) affected)

MyID                 MyName  DateOfBirth 
——————– ——- ———– 
1                    A       1980-04-10 
1                    B       1982-03-08

(2 row(s) affected)

The second result set output is based on the defination given in With Result Sets.

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

2 Comments on “How to redefine the returning result sets of a stored procedure?”

  1. Hi Sandip,

    If proc return multiple result set like Table 1 , Table 2 Table 3 etc and want to return only Table 2 as return result set from proc. How do we achive this ?

    1. HI Ashish,
      Your question is not clear.
      If procedure has 3 return result set , it will always return 3 result set.
      If you want any specific result set out of it. You need to change your procedure code to return 1 result set based on your need.

      Thank you

Leave a Reply to Sandip Pani Cancel reply

Your email address will not be published. Required fields are marked *