T-SQL流控制主要有:
If...Else
Case
While
Continue/Break
GOTO/RETURN
----------------------------------------神圣分割线----------------------------------------
1.If...Else
IF condition
Begin
do something here
End
Else
Begin
do something here
End
----------------------------------------神圣分割线----------------------------------------
2.CASE
CASE
WHEN condition THEN value
WHEN condition2 THEN value2
...
END
eg
SELECT ProductName AS 'PRODUCTS', 'Popularity' =
CASE
WHEN UnitsInStock <= 5 THEN 'Fast Mover'
WHEN UnitsInStock > 5 AND UnitsInStock <= 15 THEN 'Average Mover'
WHEN UnitsInStock > 15 AND UnitsInStock <= 1000 THEN ' Slow Mover'
END
FROM Products
eg2
DECLARE @Discount real
DECLARE @CouponCode char(5)
SET @CouponCode = 'CDKIG'
SET @Discount =
Case @CouponCode
WHEN 'CXDFR' THEN 10
WHEN 'CDKIG' THEN 7.5
WHEN 'CKIDK' THEN 8
END
PRINT @Discount
----------------------------------------神圣分割线----------------------------------------
3.WHILE
WHILE condition
BEGIN
do something here
END
----------------------------------------神圣分割线----------------------------------------
4.GOTO和RETURN
GOTO移动到由"标签后接冒号(Sample:)"标识的行. RETURN 无条件结束过程.(就是类似VC等语言的咯)
DECLARE @var1 int
SET @var1 = 1
Here:
SET @var1 = @var1 + 1
GOTO CheckResult
CheckResult:
IF @var1 > 10
BEGIN
PRINT var1
RETURN
END
ELSE
BEGIN
GOTO Here
END
----------------------------------------神圣分割线----------------------------------------
5. WAITFOR
eg1
BEGIN
WAITFOR TIME '14:52'
PRINT 'The Time is ' + LEFT(Convert(char(20), GETDATE(), 14), 5)
END
eg2
BEGIN
WAITFOR DELAY '0:00:5'
PRINT 'DELAY 5 SECONDS'
END
当14:52分时显示The Time is 14:52(用来做定时器不知道效率怎么样.)
----------------------------------------神圣分割线----------------------------------------
6. RAISERROR
可用于触发器, 存储过程, 事务处理等
RAISERROR语法:
RAISERROR ({ msg_id | msg_str} {, severity, state}
[ , argument[, ...n])
[ WITH option [, ...n] ]
最简单的客户端信息观察方式是 RAISERROR(Message, Severity, State). 后面详细介绍
----------------------------------------神圣分割线----------------------------------------
7. TRY...CATCH
作用同C#等, TRY中语句出错时将转到CATCH块中, Sql 2000 以下没有
BEGIN TRY
{sql_statement | statement_block}
END TRY
BEGIN CATCH
{sql_statement | statement_block}
END CATCH
eg:
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
EXECUTE MyErrorHandler
END CATCH
总结完 == 学完 == 以后忘记直接查