solving sql server problems for millions of dbas and developers since 2006



SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Twitter Page MSSQLTips Google+ Page








Overview of WITH RESULT SETS Feature of SQL Server Denali

By: | Read Comments (3) | Print

Ashish has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

Related Tips: 1 | 2 | 3 | 4 | 5 | More

Problem

While looking through the new features and improvements in SQL Server Denali, we found a potentially interesting feature WITH RESULT SETS. This is a very useful feature especially when one needs to display a result set of a stored procedure by changing the names and data types of the returning result set. In this tip we will take a look at an example which uses the new WITH RESULT SETS feature in SQL Server Denali.

Solution

There was always a limitation in the previous versions of SQL Server that whenever you wanted to change a Column Name or a Data Type within the result set of a Stored Procedure you ended up making changes to all the references within a Stored Procedure. This limitation has been overcome with the release of WITH RESULT SETS feature in SQL Server Denali.

Let's go through an example which demonstrates using WITH RESULT SETS syntax to change the names and data types of the returning result set.

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
DROP TABLE [dbo].[Person]
GO
CREATE TABLE [dbo].[Person]
(
 [ID]   [INT]   NOT NULL,
 [PersonType]  [NCHAR](15)  NOT NULL,
 [FirstName]  NVARCHAR(50) NOT NULL,
 [MiddleName]  NVARCHAR(50)  NULL,
 [LastName]  NVARCHAR(50)  NOT NULL,
) ON [PRIMARY]
GO
INSERT INTO dbo.Person VALUES (1,'Employee','Shannon','L','Johnston')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsingWithResultSetsFeatureOfDenali]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UsingWithResultSetsFeatureOfDenali]
GO
CREATE PROCEDURE UsingWithResultSetsFeatureOfDenali
AS
BEGIN
 SELECT
  ID, 
  FirstName + ' ' + MiddleName +' '+ LastName AS Name, 
  PersonType 
 FROM dbo.Person
END
GO
/* Execute Stored Procedure */
EXEC UsingWithResultSetsFeatureOfDenali
GO
/* Using WITH Result Sets Feature Of Denali (SQL Server 2011) */
EXEC UsingWithResultSetsFeatureOfDenali 
WITH RESULT SETS
(
 ( 
  ID INT,
  ContactPersonName VARCHAR(150),
  PersonType VARCHAR(15)
 ) 
) 
GO

In the above example, you can see that using WITH RESULTS SETS feature of SQL Server Denali we have changed the Column Name and Data Type to meet our needs irrespective of the Column Name and Data Type returned within the result set of the Stored Procedure. The Column Name is changed from “Name” to “ContactPersonName” and also Data Type for “Name is changed from NVARCHAR to VARCHAR and Data Type for “PersonType” is changed from NCHAR to VARCHAR while displaying the result set. This feature can be very useful when executing a stored procedure in SSIS where you can execute the Stored Procedure with the required columns names and appropriate data types.

The below image shows the output when running the above commands. The first result set doesn't change the names or data types and the second result set we have changed the column names and data types using this new feature.

using the with results feature of sql server denali

Next Steps



Related Tips: 1 | 2 | 3 | 4 | 5 | More | Become a paid author


Last Update: 4/12/2011

Share: Share 






Comments and Feedback:

Tuesday, April 12, 2011 - 9:10:32 AM - Sibi Read The Tip

Good one Ashish... Awaiting more such tips...


Wednesday, April 13, 2011 - 4:52:41 PM - Anil Das Read The Tip

Why are you using a stored procedure instead of a function in the first place. Yes, it shows off the feature, but your example is contrived.


Tuesday, April 19, 2011 - 8:29:33 AM - Jeremy Kadlec Read The Tip

Anil,

I am not exactly sure what you mean.  I think a stored procedure is more than reasonable based on the example.

Thank you,
Jeremy Kadlec



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information
"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

Time-strapped database professionals use SQL Monitor to look after their servers. Try it online.

What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check consulting services.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Learn SQL Server 2012, Performance Tuning, Development, Administration, Replication and more - free webcasts


Copyright (c) 2006-2012 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.


Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com