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)
       )
)





Sunday, 20 May 2012

Alter table Change Data Type in SQL Server

Hi,

Following are the few inportnat queries whih is needed when one need to alter table.


1. Alter table and change data type.
alter table tbldelegates
ALTER COLUMN de_lastname VARCHAR(50)

2. alter table add new column with default.
ALTER TABLE tblCourse
ADD NewCol VARCHAR(50) Default '' Not null

3. Alter table drop column with constraint
       a. First drop the constraint
      ALTER TABLE Course
      drop constraint DF__Course__NewCol__1BFD2C07

      b. then drop the column
     ALTER TABLE Course
     DROP column NewCol

Sunday, 6 May 2012

Searching text in Stored procedures

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%ms_redo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)