Monday, 21 May 2012

SQL Server 2012 : New TSQL Features

New T-SQL Features in SQL Server 2012

/* 1.  IIF  Function*/

DECLARE @a int = 10;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;


/* 2. CHoose  Function*/

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;


/* 3.  Concat  Function*/
SELECT CONCAT ( 'Happy ', 'Birthday ',  'Shailendra ',07, '/', '19' ) AS Result;


/* 4.  Format  Function*/

DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result;


/* 5. Create Sequence Feature*/

CREATE SEQUENCE DemoSequence
START WITH 1
INCREMENT BY 1;


BEGIN TRAN
SELECT NEXT VALUE FOR dbo.DemoSequence
ROLLBACK TRAN


/* 6.  Throw in Error Handling No need to trap like we used to do in 2008*/

BEGIN TRY
      DECLARE @intvalue INT
      SET @intvalue = 'Hi'
 END TRY
 BEGIN CATCH
      THROW
 END CATCH




/* 7. With Result Set Option you can have you own column name */

 CREATE PROCEDURE Proc_Resultset2012

AS

BEGIN

      Select 1 as 'Seq','James' as 'UNAME'  UNION ALL
       Select 2 as 'Seq','Bond' as 'UNAME'  UNION ALL
       Select 3 as 'Seq','SuperBond' as 'UNAME' 

    

END
       /*First try with this */

exec Proc_Resultset2012


       /* Try this */
exec Proc_Resultset2012
WITH RESULT SETS
(
       (
              MyID INT,
              Myname VARCHAR(20)
       )
)





No comments: