New T-SQL Features in SQL Server 2011 Microsoft Sql Server

This is a good feature provided with the execution of a stored procedure.

Legacy method

In earlier versions of SQL server when we wished to change a  column name or datatype in the resultset of a stored procedure, all the references needed to be changed. There was no simple way to dump the output of a stored procedure without worrying about the column names and data types.

 2011 Method

With SQL Server 2001, the new WithResultsSet feature avoids the requirement to change the stored procedure in order to change the column names in a resultset.

For example :

CREATE PROCEDURE Denali_WithResultSet

AS

BEGIN

       SELECT 1 as No,’Tsql’ Type, ‘WithResultSet’ AS Feature UNION ALL

       SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS Feature UNION ALL

       SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS Feature UNION ALL

       SELECT 4 as No,’Tsql’ Type, ‘Sequence’ AS Feature

END

GO

EXEC Denali_WithResultSet

WITH RESULT SETS

(

       (      No int,

              FeatureType varchar(50),

              FeatureName varchar(50)

       ) 

)

The WithResultsSet option after the Exec statement conatins the resultset in (…) brackets. Here, we can change the column name and datatype according to our needs,  independent of what is column name returned in the resultset. In the above example ‘Type’ is changed to ‘FeatureType’ and ‘Feature’ is changed to ‘FeatureName’. This can be helpful for using an appropriate datatype while showing the resultset.

This feature will be especially helpful when executing a stored procedure in SSIS tasks. While executing any stored procedure in OLEDB Source, it will be now possible to execute the procedure with the required column names and datatypes.