break in case statement in T-SQL

Question

Is possible using break command in case statement in mssql?

Because the condition is verified that expression GO, instead of doing the next CASE's transition. ssip_miktar is 5, ssip_teslim_miktar is 0 and S74MIKTAR is 5

update set ssip_teslim_miktar= ssip_teslim_miktar+
CASE WHEN ssip_miktar<@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR-ssip_miktar
WHEN ssip_miktar<@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR-(ssip_miktar+ssip_teslim_miktar) 
WHEN ssip_miktar=@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR 
WHEN ssip_miktar=@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR-ssip_teslim_miktar
 WHEN ssip_miktar>@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR
 WHEN ssip_miktar>@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR  
END,
@S74MIKTAR=@S74MIKTAR-CASE 
         WHEN  ssip_miktar<@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR-ssip_miktar
         WHEN  ssip_miktar<@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR-(ssip_miktar+ssip_teslim_miktar)
         WHEN  ssip_miktar=@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR
         WHEN  ssip_miktar=@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR-ssip_teslim_miktar
         WHEN  ssip_miktar>@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR
         WHEN  ssip_miktar>@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR
                     ELSE 0
      END
where ssip_teslim_miktar<ssip_miktar

Please look at follow.. I wrote my code as comment. – esquare May 27 '11 at 15:01 — esquare, May 27 '11 at 15:01

score 10 · Answer 1 · answered May 27 '11 at 14:47

10

You shouldn't need to use a break because SQL Case statements don't fall through.

DECLARE @x int

SET @x = 0

SELECT CASE
    WHEN @x = 0 THEN 'zero'     -- Only this line of the expression is evaluated
    WHEN @x <> 0 THEN 'not-zero'
    END

answered May 27 '11 at 14:47

Phil Gan

2,813
2
29
38

score 7 · Answer 2 · answered May 27 '11 at 14:46

This is transactional SQL and is not iterative. It does not require a break because only one of the when/else clauses will be evaulated.

Try the following:

declare @test int
set @test=1
select @test = @test + 
    case 
        when 1=1 then 1
        when 2=2 then 1
        when 3=3 then 1
        when 4=4 then 1
        when 5=5 then 1
        when 6=6 then 1
        when 7=7 then 1
        when 8=8 then 1
        else 1
       end
select @test

@test is only ever 2

score 4 · Answer 3 · answered May 27 '11 at 14:44

4

This is not necessary. THEN effectively acts as a return or break, so the statement is short-circuited.

answered May 27 '11 at 14:44

D'Arcy Rittich

167,292
40
290
283

set ssip_teslim_miktar= ssip_teslim_miktar+CASE WHEN ssip_miktar<@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR-ssip_miktar WHEN ssip_miktar<@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR-(ssip_miktar+ssip_teslim_miktar) WHEN ssip_miktar=@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR WHEN ssip_miktar=@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR-ssip_teslim_miktar WHEN ssip_miktar>@S74MIKTAR AND ssip_teslim_miktar=0 THEN @S74MIKTAR WHEN ssip_miktar>@S74MIKTAR AND ssip_teslim_miktar>0 THEN @S74MIKTAR where ssip_teslim_miktar – esquare May 27 '11 at 14:53
Also ssip_miktar=5,ssip_teslim_miktar=0 and S74MIKTAR=5 – esquare May 27 '11 at 14:56
@bedreddin I see no `END` statement. – D'Arcy Rittich May 27 '11 at 14:59

break in case statement in T-SQL

3 Answers3

Linked