SQLcommitted

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

This is one of the programmability enhancements 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.

April 23, 2012 - Posted by SQLcommitted | SQL Info