--Concat示例

Select CONCAT('SQL Server',2012,null,'RTM')

--Format实例

DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'd', 'en-US' ) AS Result

SELECT FORMAT( @d, 'd', 'zh-CN' ) AS Result

--Choose实例

SELECT CHOOSE(0,'CareySon','Jack','Tony','Peter')

SELECT CHOOSE(2,'CareySon','Jack','Tony','Peter')

SELECT CHOOSE(3,'CareySon','Jack','Tony','Peter')

SELECT CHOOSE(5,'CareySon','Jack','Tony','Peter')

--Choose

select CHOOSE(ManagerID,'CareySon','Jack','Tony','Peter')

 FROM [AdventureWorks].[HumanResources].[Employee]

--传统的CASE...WHEN

SELECT ManagerID=case when ManagerID=1 then 'CareySon'

     when ManagerID=2 then 'Jack'

     when ManagerID=3 then 'Tony'

     when ManagerID=4 then 'Peter'

     else null

     end

FROM [AdventureWorks].[HumanResources].[Employee]

--IIF示例

select IIF(1>2,'结果1','结果2')

--DATEFROMPARTS示例

SELECT DATEFROMPARTS ( 2012, 3, 12 ) AS Result;

--EOMONTH示例

declare @date datetime

set @date='2012-3-12'

SELECT EOMONTH ( @date ) AS '本月最后一天'

SELECT EOMONTH ( @date, 1 ) AS '下个月最后一天'

SELECT EOMONTH ( @date, -1 ) AS '上个月最后一天'

--PARSE和Cast示例

SELECT TRY_PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result

SELECT Cast('Monday, 13 December 2010' AS datetime2) AS Result

--Convert和Try_Convert示例

--转换不成功，为NULL

select TRY_CONVERT(float, 'test')

--转换不成功报错

select CONVERT(float, 'test')

--永远不可能转换成功的，报错

SELECT TRY_CONVERT(xml, 4) AS Result;

--TRY_PARSE和PARSE

SELECT TRY_PARSE('错误值' AS datetime2 USING 'en-US') AS Result

SELECT PARSE('错误值' AS datetime2 USING 'en-US') AS Result

--Throw语句尝试

--1.更简洁优雅的代码

--2.可以正确的标识出出错的行数，对于大量T-SQL来说，这点可以节省不少时间

BEGIN TRAN

BEGIN TRY

 select 1/0;

 COMMIT

END TRY

BEGIN CATCH

    ROLLBACK;

    --RAISERROR('自定义错误消息',12,1)

    --THROW

    THROW 50001,'自定义错误消息',1;

    --PRINT ERROR_NUMBER+ERROR_SEVERITY()+ERROR_STATE()+ERROR_PROCEDURE()+ERROR_LINE()+ERROR_MESSAGE()

END CATCH