Thursday, December 16, 2010

T-SQL "ELSE IF" statement

In Transact-SQL, if we need to test multiple conditions, we can use case or multiple IF ... ELSE IF statement which I don't much in Microsoft SQL Server Books Online. The following codes have been tested in SQL Server 2008.

Using "ELSE IF" example:

/* print out value3 */
DECLARE @para INT = 12;

IF @para = 10
    PRINT 'value1'
ELSE IF @para = 11   
    PRINT 'value2'
ELSE IF @para = 12   
    PRINT 'value3'       
ELSE
    PRINT 'Not any value.';

Using "CASE" example:

/* print out value3 */ 
DECLARE @para INT = 12;
SELECT
    CASE
@para
                WHEN
10 THEN 'value1'
                WHEN
11 THEN 'value2'
                WHEN
12 THEN 'value3'
                WHEN
13 THEN 'value4'
    ELSE 'Other'
    END
;

It seems using "case" is more intuitive.

No comments:

Post a Comment