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