Incorrect syntax near the keyword CASE (SQL Function)

Question

I am porting a large Access application to SQL server, in doing so I have been working on re-writing all the VBA functions as SQL functions. I cannot get the following create function statement to execute. Seemingly I am using CASE incorrectly? Any help is appreciated, this is my first venture into SQL functions.

CREATE FUNCTION dbo.[NAL_PolicyCode]
(
@gm1 float, 
@gm2 float = 1,
@gm3 float = 1,
@typ varchar(10) = ''
)
RETURNS VarChar(10)
AS BEGIN
DECLARE @gm float;
SET @gm = @gm1;
SET @gm = CASE 
    WHEN @gm2 < @gm THEN @gm2 END 
SET @gm = CASE
    WHEN @gm3 < @gm THEN @gm3 END 

SET @gm = Round(@gm, 3);

IF(@typ = 'MS')
    BEGIN
        (CASE WHEN @gm < 0.03 THEN 
                    PolicyCode = '1B'
             WHEN @gm < 0.05 THEN
                    PolicyCode = '1C'
             WHEN @gm < 0.08 THEN
                    PolicyCode = '1D'
             WHEN @gm < 0.12 THEN
                    PolicyCode = '1E'
             WHEN @gm < 0.16 THEN
                    PolicyCode = '1F'
             WHEN @gm < 0.24 THEN
                    PolicyCode = '1G'
             WHEN @gm < 0.29 THEN
                    PolicyCode = '1H'
             WHEN @gm < 0.47 THEN
                    PolicyCode = '1J'
             ELSE
                    PolicyCode = '1K'
                    END) 
    END
ELSE IF(@typ = 'PL')
    BEGIN
        (CASE WHEN @gm < 0.35 THEN
                PolicyCode = "8"
             WHEN @gm < 0.45 THEN
                PolicyCode = "P"
             WHEN @gm < 0.58 THEN
                PolicyCode = "V"
             WHEN @gm < 0.7 THEN
                PolicyCode = "4"
            ELSE
                PolicyCode = "R"
                END) 
    END
ELSE
    BEGIN
        (CASE WHEN @gm < 0.16 THEN
                PolicyCode = "Y"
             WHEN @gm < 0.24 THEN
                PolicyCode = "Z"
             WHEN @gm < 0.29 THEN
                PolicyCode = "X"
            WHEN @gm < 0.36 THEN
                PolicyCode = "9"
             WHEN @gm < 0.41 THEN
                PolicyCode = "J"
             WHEN @gm < 0.47 THEN
                PolicyCode = "N"
             WHEN @gm < 0.55 THEN
                PolicyCode = "D"
             WHEN @gm < 0.63 THEN
                PolicyCode = "S"
             WHEN @gm < 0.75 THEN
                PolicyCode = "T"
            ELSE
                PolicyCode = "U"
                END) 
    END
END;

I don't see where you are declaring 'PolicyCode' and given all variables in SQL Server start with @, that may be your first clue... — MikeS
– MikeS, Commented Jun 12, 2018 at 19:54
Also, rather than using an IF...ELSE IF...ELSE, you might want to also look at using another CASE. There's no need to evaluate @typ twice. — Shawn
– Shawn, Commented Jun 12, 2018 at 19:59

Gordon Linoff · Accepted Answer · 2018-06-12 19:57:55Z

1

You have:

IF(@typ = 'MS')
    BEGIN
        (CASE WHEN @gm < 0.03 THEN 
                    PolicyCode = '1B'
         . . .

The construct you are using is called "control flow", because it is at the level of programming a T-SQL block. There is no case that is a control flow clause in T-SQL. Presumably, you intend something like:

IF(@typ = 'MS')
    BEGIN
        SET @PolicyCode = (CASE WHEN @gm < 0.03 THEN PolicyCode = '1B'
                                  . . .

answered Jun 12, 2018 at 19:57

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SMor · Accepted Answer · 2018-06-12 21:26:26Z

Stop. First, don't use double quotes as a string delimiter. You've mixed single and double quote usage - which is a huge problem. In fact, I suggest you stop completely and spend some time to learn tsql and good programming practices.

Stop. Because a direct port of code from one platform to another will usually end up with subtle errors (that are difficult to find/fix) and poor performance. In addition, the code will often be difficult to understand/use/debug. For this particular issue, it would probably make more sense to put these ranges in a table and join to/select from it. I'll guess that one could have taken that route in Access - which does not inspire confidence in the design of the schema or the code. Using a table, you could (and should) also create the proper constraint to prevent invalid data seeping into your database. Generally speaking, scalar-valued functions that act as lookup tables will perform poorly.

To help illustrate the concept, the following uses a table variable to store the values referenced by your logic. Notice the arcane logic needed to join in the last select statement. I'll get back to that.

set nocount on;
declare @PolicyCodes table (Typ varchar(2) not null, PolicyCode varchar(2) not null, 
    MinVal decimal(2,2) not null, MaxVal decimal(2,2) not null);

insert @PolicyCodes (Typ, PolicyCode, MinVal, MaxVal)
values 
    ('MS', '1B', 0,    0.02),  ('MS', '1C', 0.03, 0.04), 
    ('MS', '1D', 0.05, 0.07),  ('MS', '1E', 0.08, 0.11),
    ('MS', '1K', 0.12, 0.99),
    ('PL', '8',  0,    0.34),  ('PL', 'P',  0.35, 0.44),
    ('PL', 'R',  0.45, 0.99),
    ('??', 'Y',  0,    0.15),  ('??', 'Z',  0.16, 0.23),
    ('??', 'Z',  0.24, 0.99);
--select * from @PolicyCodes order by Typ, MinVal, MaxVal;

with cte as (
select * from (values 
   (1, 'MS', 0.20, 0.04, 0.99), --1C
   (2, 'MS', 0.99, 0.70, 0.11), --1E
   (3, 'MS', 0.05, 0.06, 0.07), --1D
   (4, '??', 0.16, 0.17, 0.00)  --Y
) as TestVals (ID, Typ, gm1, gm2, gm3)
)
select cte.*, PC.* 
from cte inner join @PolicyCodes as PC
on cte.Typ = PC.Typ
and case
   when cte.gm1 < cte.gm2 and cte.gm1 < cte.gm3 then cte.gm1 
   when cte.gm2 < cte.gm3 then cte.gm2
   else cte.gm3 end between PC.MinVal and PC.MaxVal
order by cte.ID;

First, notice that this is just a subset of your logic. Scale things down for discussion and investigation; take little bites before you attempt the whole thing. You will need to adjust this logic in case of typos or errors I made trying to digest your code.

Second, notice that I did not use float or your rounding logic. That seems very suspect to me. Maybe that made sense in Access - I have no direct knowledge of it. But the rounding implies that the value of GM has a much more restricted domain. Do not propagate bad decisions. Yes - that does increase the effort in migrating but it will be better in the end.

When you see variables or columns with names that have a repetitive pattern, that is a usually an indication of schema issues. Choosing which of three values to use for your lookup might be OK when "setting" something - but that should most likely be done before you save the rows in your database. "GM" isn't a particularly well-named variable so it provides no clues as to what it means or how it is used. Perhaps there is a "primary" code used to establish the relationship and other secondary codes that need to be kept for some undefined reason. That is impossible to know without knowledge of the system.

That should give you ideas of how to do things. But it is only a demonstration of the concept.

Kostya · Accepted Answer · 2018-06-12 20:19:03Z

you probably need something like this :

CREATE FUNCTION dbo.[NAL_PolicyCode]
(
@gm1 float, 
@gm2 float = 1,
@gm3 float = 1,
@typ varchar(10) = ''
)
RETURNS VarChar(10)

BEGIN

DECLARE
@GM float,
@PolicyCode varchar(10)


set @GM =(  select min(gm) from (
            select @gm1 AS gm union
            select @gm2 union
            select @gm3) x
            );
SET @gm = Round(@gm, 3);

SET @PolicyCode = (
SELECT 
CASE WHEN @typ = 'MS' THEN 
        (
    CASE     WHEN @gm < 0.03 THEN  '1B'
             WHEN @gm < 0.05 THEN '1C'
             WHEN @gm < 0.08 THEN '1D'
             WHEN @gm < 0.12 THEN '1E'
             WHEN @gm < 0.16 THEN '1F'
             WHEN @gm < 0.24 THEN '1G'
             WHEN @gm < 0.29 THEN '1H'
             WHEN @gm < 0.47 THEN '1J'
             ELSE'1K' END
        )
WHEN @typ = 'PL' THEN 
        (
    CASE     WHEN @gm < 0.35 THEN '8'
             WHEN @gm < 0.45 THEN 'P'
             WHEN @gm < 0.58 THEN 'V'
             WHEN @gm < 0.7 THEN '4'
            ELSE 'R'   END 
        )
ELSE 
        (
CASE        WHEN @gm < 0.16 THEN 'Y'
             WHEN @gm < 0.24 THEN 'Z'
             WHEN @gm < 0.29 THEN 'X'
            WHEN @gm < 0.36 THEN '9'
             WHEN @gm < 0.41 THEN 'J'
             WHEN @gm < 0.47 THEN 'N'
             WHEN @gm < 0.55 THEN 'D'
             WHEN @gm < 0.63 THEN 'S'
             WHEN @gm < 0.75 THEN 'T'
            ELSE 'U'
                END
        )
END
)

RETURN @PolicyCode;

END

Exactly what I was looking for, thank you! I was unsure how to properly structure the case sections.

Collectives™ on Stack Overflow

Incorrect syntax near the keyword CASE (SQL Function)

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related