SQL Server 2012 (or Denali) CTP is now available and can be downloaded at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en
SQL Server 2012 has several major enhancements including a new look for SSMS. SSMS is now similar to Visual Studio with greatly improved Intellisense support.
This article we will focus on the T-SQL Enhancements in SQL Server 2011.
The main new TSQL features in SQL Server 2012 are:
- WITH RESULT SETS
- OFFSET AND FETCH
- THROW in Error handling
- SEQUENCE
WITH RESULT SETS
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.
2012 Method
With SQL Server 2012, 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.