Incorrect syntax near the keyword 'CASE'

[anonim1]

I'm trying to return a specific output value based on a given input value. However, I can't get the CASE statement to work.. What am I doing wrong?

CREATE FUNCTION ConvertId(@id as tinyint)
RETURNS tinyint
AS
BEGIN
	DECLARE @newId AS tinyint
	SET @newId = @id
	CASE @newId
		WHEN  1 THEN 72
		WHEN  2 THEN  5
		WHEN  3 THEN  6
		WHEN  4 THEN 19
		WHEN  5 THEN  9
		WHEN  6 THEN 17
		WHEN  7 THEN 18
		WHEN  8 THEN 10
		WHEN  9 THEN  7
		WHEN 10 THEN  8
	RETURN @newId

END

 

[SQLDenis]

what about this?

CREATE FUNCTION ConvertId(@id as tinyint)
RETURNS tinyint
AS
BEGIN
    DECLARE @newId AS tinyint
    SELECT @newId = 
    CASE @id
        WHEN  1 THEN 72
        WHEN  2 THEN  5
        WHEN  3 THEN  6
        WHEN  4 THEN 19
        WHEN  5 THEN  9
        WHEN  6 THEN 17
        WHEN  7 THEN 18
        WHEN  8 THEN 10
        WHEN  9 THEN  7
        WHEN 10 THEN  8 END

    RETURN @newId

END

also do you need an else here?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[anonim1]

Oops I forgot the END CASE statement.. but it still doesn't work with it.

CREATE FUNCTION ConvertId(@id as tinyint)
RETURNS tinyint
AS
BEGIN
	DECLARE @newId AS tinyint
	SET @newId = @id
	CASE @newId
		WHEN  1 THEN 72
		WHEN  2 THEN  5
		WHEN  3 THEN  6
		WHEN  4 THEN 19
		WHEN  5 THEN  9
		WHEN  6 THEN 17
		WHEN  7 THEN 18
		WHEN  8 THEN 10
		WHEN  9 THEN  7
		WHEN 10 THEN  8
	END CASE
	RETURN @newId
END

 

[anonim1]

Nope, I don't need an else.. I tried both END CASE and just END, it still says "Incorrect syntax near the keyword 'CASE'".

 

[gmmastros]

Denis also changed SET to SELECT.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[anonim1]

gmmastros,

Thank you for pointing that out, it slipped my eye. Thanks very much Denis. Cheers!

 

[gmmastros]

I noticed it because I was going to answer the question with a query that was nearly identical to his.

Thinking about this a little more, I have another suggestion. You should (in my opinion) create another table for this sort of thing. Performance would be better.

Instead of using the function in your code, you would simply join to another table and return the results from the table. This sort of thing is very common with SQL Server databases.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[anonim1]

gmmastros,

That was my original intention, actually. I wanted to create an array with the new values and then return arrayOfNewIds[indexOfOldId]. However, I saw that there is no built-in array support. I don't want to create a new permanent table; however, I would be interested in programmatically creating a table in memory and using that instead. I would just need some help with the syntax.