“WITH RESULT SETS”
is new enhanced feature comes with SQL Server “DENALI”. In many scenarios, we
want to return the result sets from SP with changed column name and with
different data type. In these cases we have been using Temporary table.
Create temporary table, Insert data in temp table by
executing Stored Procedure and display data from temp table, what if we can do
it with simply executing stored procedure? Isn’t it awesome???
Yes, it is…. Now SQL Server Denali makes it possible. Let us
see it how…..
--create
Member'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 * FROM
MemberPersonalDetail
go
Now, I will make one simple stored procedure to return all
columns of this table in SQL Server Denali CTP1 which we used to do since very
long 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 execute
this stored procedure with regular method which we used to do and after that, I
will execute the same stored procedure with “WITH RESULT SETS”,which will have
changed column name of few column and changed data type of column.
--executing SP
EXEC
getMemberPersonalDetail
GO
--Executing SP
with "WITH RESULT SETS"
--MemberName
will become "Name" from Varchar(20) to Varchar(6)
--both date
column name will also be changed.
EXEC
getMemberPersonalDetail
WITH RESULT SETS
(
(
ID INT,
Name Varchar(6),
DateOfRegistration date,
DateOfExpiration date
)
);
Here is the screen shot which shows results of both the
execution of stored procedure.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”
0 comments:
Post a Comment