CASE statement plus modulo (%)

Question

AIM: simple program; when my variable is divided into 3 it returns the word'hip', when it is divided into 5 it returns 'hop' and when it is divided into 3 & 5 at the same time it returns both words.

DECLARE @Zmienna AS INT
SET @Zmienna = 0

WHILE @Zmienna < 999
BEGIN
    PRINT @Zmienna +
        CASE
            WHEN @Zmienna/3=% THEN ' hip'
            WHEN @Zmienna/5=% THEN ' hop'
        END 
    SET @Zmienna = @Zmienna + 1
END

Error

ERROR: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'END'.

Any idea?

Answer 1

I would use the remainder of the modulo (like you tried) and concatenate the two case statements (else you will never get hiphop if both conditions are true). Also you need the else '' since otherwise you can get null values:

CASE
WHEN @Zmienna % 3 = 0
THEN ' hip'
ELSE ''
END
+
CASE
WHEN @Zmienna % 5 = 0
THEN ' hop'
ELSE ''
END 

Hint: if you want a space or some other text if both conditions are true, you have to use an and in the case statement:

CASE
WHEN @Zmienna % 3 = 0 and @Zmienna % 5 = 0
THEN ' hip hop'
WHEN @Zmienna % 3 = 0
THEN ' hip'
WHEN @Zmienna % 5 = 0
THEN ' hop'
END 

Answer 2

try this:

DECLARE @Zmienna AS INT
SET @Zmienna = 0

WHILE @Zmienna < 999
BEGIN
    PRINT CAST(@Zmienna as varchar) +
        CASE
            when (@Zmienna%3=0 AND @Zmienna%5=0)  THEN ' hip hop'
            WHEN @Zmienna%3=0 THEN ' hip'
            WHEN @Zmienna%5=0 THEN ' hop'

        END 
    SET @Zmienna = @Zmienna + 1
END