Executing Stored Procedure with Result Sets in SQL Server Denali

“WITH RESULT SETS”is new enhanced feature comes with SQL Server “DENALI”. In many scenarios, wewant to return the result sets from SP with changed column name and withdifferent data type. In these cases we have been using Temporary table. 

Create temporary table, Insert data in temp table byexecuting Stored Procedure and display data from temp table, what if we can doit with simply executing stored procedure? Isn’t it awesome???

Yes, it is…. Now SQL Server Denali makes it possible. Let ussee it how…..

–createMember’s personal detail table and insert data in it.

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

INSERT INTO MemberPersonalDetail

SELECT ‘Ritesh Shah’,’01/01/2000′,’12/31/2015′ Union ALL

SELECT ‘Rajan Shah’,’02/07/2005′,’06/20/2011′ Union ALL

SELECT ‘Teerth Shah’,’06/22/2011′,’12/31/2015′

GO

SELECT * FROMMemberPersonalDetail

go

Now, I will make one simple stored procedure to return allcolumns of this table in SQL Server Denali CTP1 which we used to do since verylong back. There is nothing new in this stored procedure.

CREATE PROC getMemberPersonalDetail

AS

      SELECT 

            MemberID

            ,MemberName

            ,RegisterDate

            ,ExpirationDate

      FROM

            MemberPersonalDetail

GO

After making this simple stored procedure, I will executethis stored procedure with regular method which we used to do and after that, Iwill execute the same stored procedure with “WITH RESULT SETS”,which will havechanged column name of few column and changed data type of column.

–executing SP

EXECgetMemberPersonalDetail

GO

–Executing SPwith “WITH RESULT SETS”

–MemberNamewill become “Name” from Varchar(20) to Varchar(6)

–both datecolumn name will also be changed.

EXECgetMemberPersonalDetail

WITH RESULT SETS

(

      (

            ID INT,

            Name Varchar(6),

            DateOfRegistration date,

            DateOfExpiration date

      )

);

Here is the screen shot which shows results of both theexecution of stored procedure.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a defaultreference of all articles but examples and explanations prepared by RiteshShah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Executing Stored Procedure with Result Sets in SQL Server Denali

“WITH RESULT SETS”is new enhanced feature comes with SQL Server “DENALI”. In many scenarios, wewant to return the result sets from SP with changed column name and withdifferent data type. In these cases we have been using Temporary table. 

Create temporary table, Insert data in temp table byexecuting Stored Procedure and display data from temp table, what if we can doit with simply executing stored procedure? Isn’t it awesome???

Yes, it is…. Now SQL Server Denali makes it possible. Let ussee it how…..

–createMember’s personal detail table and insert data in it.

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

INSERT INTO MemberPersonalDetail

SELECT ‘Ritesh Shah’,’01/01/2000′,’12/31/2015′ Union ALL

SELECT ‘Rajan Shah’,’02/07/2005′,’06/20/2011′ Union ALL

SELECT ‘Teerth Shah’,’06/22/2011′,’12/31/2015′

GO

SELECT * FROMMemberPersonalDetail

go

Now, I will make one simple stored procedure to return allcolumns of this table in SQL Server Denali CTP1 which we used to do since verylong back. There is nothing new in this stored procedure.

CREATE PROC getMemberPersonalDetail

AS

      SELECT 

            MemberID

            ,MemberName

            ,RegisterDate

            ,ExpirationDate

      FROM

            MemberPersonalDetail

GO

After making this simple stored procedure, I will executethis stored procedure with regular method which we used to do and after that, Iwill execute the same stored procedure with “WITH RESULT SETS”,which will havechanged column name of few column and changed data type of column.

–executing SP

EXECgetMemberPersonalDetail

GO

–Executing SPwith “WITH RESULT SETS”

–MemberNamewill become “Name” from Varchar(20) to Varchar(6)

–both datecolumn name will also be changed.

EXECgetMemberPersonalDetail

WITH RESULT SETS

(

      (

            ID INT,

            Name Varchar(6),

            DateOfRegistration date,

            DateOfExpiration date

      )

);

Here is the screen shot which shows results of both theexecution of stored procedure.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a defaultreference of all articles but examples and explanations prepared by RiteshShah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah