CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
HAJJAJ
HAJJAJ

Reputation: 3787

How to use a case statement in scalar valued function in SQL?

I want to get one value from a function using a case statement. I tried the following but it does not work:

CREATE FUNCTION [FATMS].[fnReturnByPeriod]
(

    @Period INT

)
RETURNS int
AS
BEGIN

    SELECT CASE @Period 
             when 1 then 1
             when @Period >1 and @Period <=7 then 1
             when @Period >7 and @Period <=30 then 1
             when @Period >30 and @Period<=90 then 1
             when @Period >90 and @Period <=180 then 1
             when @Period >180 and @Period <=360 then 1
             else 0
           END

    RETURN @Period
END

Upvotes: 18

Views: 100044

Answers (7)

Vaibhav mane
Vaibhav mane

Reputation: 1

CREATE FUNCTION TICKET_RATE (@RATE AS INT)
RETURNS VARCHAR (50)
AS
BEGIN
     DECLARE @VISITED_PLACE VARCHAR (50)
     IF @RATE > 400 
     SET @VISITED_PLACE = 'VISIT NEAR BY PLACE'
        ELSE
            RETURN
                CASE
                    WHEN @RATE BETWEEN 0 AND 100 THEN 'VISIT MUMBAI'
                    WHEN @RATE BETWEEN 101 AND 200 THEN 'VISIT PUNE'
                    WHEN @RATE BETWEEN 201 AND 300 THEN 'VISIT DELHI'
                    WHEN @RATE BETWEEN 301 AND 400 THEN 'VISIT BENGLORE'
END
RETURN @VISITED_PLACE
END

Upvotes: 0

bernd_k
bernd_k

Reputation: 11966

When you use RETURN @Period, you must assign a value to @Period. The following example shows how to structure your code so that there is no need to declare a local variable.

CREATE FUNCTION [FATMS].[fnReturnByPeriod]
(
    @Period INT
)
RETURNS INT
AS
BEGIN
    RETURN
        CASE 
            WHEN @Period = 1 THEN 1
            WHEN @Period > 1 AND @Period <=7 THEN 1
            WHEN @Period > 7 AND @Period <=30 THEN 1
            WHEN @Period > 30 AND @Period<=90 THEN 1
            WHEN @Period > 90 AND @Period <=180 THEN 1
            WHEN @Period > 180 AND @Period <=360 THEN 1
            ELSE 0
        END 
END

Upvotes: 19

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

Here's a SET based approach to write your function in SQL Server 2008

CREATE FUNCTION [FATMS].[fnReturnByPeriod]
(
    @Period INT
)
RETURNS int
AS
BEGIN
    return isnull((SELECT min(v)
    from (values
     (1,1),
     (7,2),
     (30,3),
     (90,4),
     (180,5),
     (360,6)) t(n,v)
    where n >= @Period and @period>0), 0)
END

The way you have written it, all the CASE branches return 1, so you might as well use

return case when @period between 1 and 360 then 1 else 0 end

Upvotes: 1

Dan J
Dan J

Reputation: 16718

In what way does it "not work"? Does it produce an error when you try to execute it? Does it return an unexpected result?

Off the top of my head, there are several problems:

Your case statement returns 1 in all cases.

The case statement is also combining two different types of CASE semantics:

CASE @Period 
WHEN <condition> THEN <result>
WHEN <other condition> THEN <result>
ELSE <result>
END

or

CASE
WHEN @Period = <value> THEN <result>
WHEN @Period = <other value> THEN <result>
ELSE <result>
END

The second form allows you to use unrelated conditions, whereas the first can only check for different values of @Period.

Further, you're returning the value of @Period, not the value generated by the CASE statement.

Upvotes: 1

Neil Knight
Neil Knight

Reputation: 48557

Declare a second variable and then set that value because you aren't resetting @Period.

For example:

DECLARE @Output AS INT

SELECT @Output = CASE @Period 
                 WHEN 1 then 1     
                 WHEN @Period > 1 AND @Period <= 7 THEN 1      -- Should be 2 
                 WHEN @Period > 7 AND @Period <= 30 THEN 1     -- Should be 3
                 WHEN @Period > 30 AND @Period<= 90 THEN 1     -- Should be 4
                 WHEN @Period > 90 AND @Period <= 180 THEN 1   -- Should be 5  
                 WHEN @Period > 180 AND @Period <= 360 THEN 1  -- Should be 6   
                 ELSE 0 END;

RETURN @Output;

I have left it like this as I'm assuming you are going to change your values for each of these CASE statements.

Upvotes: 8

PedroC88
PedroC88

Reputation: 3829

You are not assigning @Period anywhere. Try with when @Period >1 and @Period <=7 then SET @Period = 1 and subsequently for all the other lines.

SQL does not have implicit returns and so you always have to set the variables before you return them.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838716

There are two types of CASE expression: simple and searched. You must choose one or the other - you can't use a mixture both types in one expression.

Try this:

SELECT CASE
    WHEN @Period = 1 THEN 1
    WHEN @Period > 1 AND @Period <= 7 THEN 2
    WHEN @Period > 7 AND @Period <= 30 then 3
    -- etc...
    ELSE 0
END

Also, you need to assign the result to something as others have already pointed out.

Upvotes: 19

Related Questions