Introduction:

Here we will explain to what is case statement in SQL Server or how to use case statement in SQL Server or case statement use with update statement or case statement use with select statement or case statement use in stored procedure with Example:


Description:

when a query has more than one condition base  return value .we should use the CASE STATEMENT. A case statement you should use in select or update statement . Case statement can be use  within a stored procedure to control the statement executed depending on each scenario.  Two different syntaxes exist for the case statement depending on how you want to test a condition or what you want to test . let’s  take at all the part of the first case statement .

syntax:

CASE expression

WHEN value_matched THEN

[statement1]

WHEN value_matched2 THEN

[Statement2]

ELSE

[Statement]

END

Example:

SELECT CASE ID

WHEN 1 THEN

 'ONE'

WHEN 2 THEN

 'TWO'

ELSE

'NONE'

END FROM Article

First, you need to define the expression that is to be tested. This could be the value of a variable, a column value from within the  SQL statement, or any valid expression within SQL Server. This expression is then used to determine the values to be matched in each WHEN clause. You can have as many WHEN clauses as you want within the CASE condition, and you do not need to cover every condition or possible value that could occur. Once a condition is matched, then only the statements within the appropriate WHEN block will be executed. Of course, only the WHEN conditions that are defined will be tested. However, you can cover yourself for any value within the expression that has not been defined within a WHEN statement by using an ELSE condition. This is used as a catch-all statement. Any value not matched would drop into the ELSE condition, and from there you could deal

with it. The second syntax is for computing a value depending on the condition.

Second Syntax:

CASE

WHEN Boolean_expression THEN result_expression

ELSE

else_result_expression

END

Example:

SELECT CASE

WHEN ID=1 THEN

 'ONE'

WHEN ID=2 THEN

 'TWO'

ELSE

'NONE'

END FROM Article

As just indicated, that conditions are check in the condition list. Once a condition is match to be true, the CASE statement will return the result and if not match any listed condition it will execute else

Statement and return the else result.

Thanks.


comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like