T-SQL 流控制

 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

 

总结完 == 学完 == 以后忘记直接查