Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Incorrect syntax near the keyword 'case'

what is wrong with this Code

CREATE FUNCTION [dbo].[ChangeRevision] (@oldRev tinyint)
RETURNS varchar(1) 
AS
begin

declare @newRev varchar(1)
DECLARE @newval int
set @newval=CAST (@oldRev as int)

case @newval
begin
when 0 then set @newRev='Z'
when 1 then set @newRev='A'
when 2 then set @newRev='B'
when 3 then set @newRev='C'

end
return @newRev;

END

i have following error Incorrect syntax near the keyword 'case'.

Incorrect syntax near the keyword 'Return'.

like image 228
nnmmss Avatar asked Oct 21 '13 12:10

nnmmss


People also ask

What is incorrect syntax near?

This indicates there is an incorrect symbol in the criteria of the query.

Can you add a case statement in where clause?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.


3 Answers

This should work:

SET @newRev = (SELECT case @newval
    WHEN 0 THEN 'Z'
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B'
    WHEN 3 THEN 'C'
    END)
like image 50
Tim Schmelter Avatar answered Sep 23 '22 23:09

Tim Schmelter


case doesn't need a begin, but does need an end

e.g.

SET @newRev = (SELECT case @newval
    WHEN 0 THEN 'Z'
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B'
    WHEN 3 THEN 'C'
    END)

MSDN CASE Docs

like image 27
Caleth Avatar answered Sep 22 '22 23:09

Caleth


there is not BEGIN keyword for case in tsql

select @newRev=case @newval
when 0 then 'Z'
when 1 then 'A'
when 2 then 'B'
when 3 then 'C'
end
like image 22
Prahalad Gaggar Avatar answered Sep 23 '22 23:09

Prahalad Gaggar