How to Use a Case Statement in Scalar Valued Function in Sql

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

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.

How does one use SELECT CASE within a Scalar Function?

You do not need a SELECT in the body of the function. You can simply write:

CREATE FUNCTION IsSumEqualToTen (
    @number1 INT,
    @number2 INT
) RETURNS BIT
AS
BEGIN
    RETURN (CASE WHEN (@number1 + @number2) = 10 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END);
END

User defined function with CASE statement

1. Add the RETURN keyword
2. Wrap the query in brackets
3. Remove GetOrderStatus=

Try this:

RETURN (WITH MyData AS
      ...
      SELECT CASE
      ...
      FROM mydata);

---

BTW this:

WHEN res IS NULL AND conf IS NULL AND PROCS IS NULL AND PROD IS NULL AND Ship IS NULL AND Canc IS NULL AND Refs is null

can be more elegantly expressed as:

WHEN COALESCE(res, conf, PROCS, PROD, Ship, Canc, Refs) IS NULL

Call scalar function in CASE expression SQL Server 2012+

Porting procedural approaches "as it is" is not a good practice at all. This is not a PHP, C# or even PL/SQL. Furthermore, in the case described you don't even need any function calls. NULL and empty string handling in scenarios mentioned above is very simple and does not require any procedural methods.

There are functions NULLIF, ISNULL, COALESCE, CASE statement. Everything you need is to check if something is true or false, null or not.

Scalar UDF calls for every row may (and will) result in serious performance issues.

Check this out:

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + IsNull(' ' + npcr.middle_name, '')
      + ' '
      + npcr.last_name
  WHEN 'L' THEN lpcr.name
END

even if you have disabled ANSI_NULLS and CONCAT_NULL_YIELD_NULL options and dont' wish to give any chance to server to do anything wrong you can use case and this approach will be much better than calling a function:

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + case when npcr.middle_name is NULL then '' else ' ' + npcr.middle_name end
      + ' '
      + npcr.last_name
  WHEN 'L' THEN lpcr.name
END

if there are empty stings allowed this code can work well (with regular null option set):

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + IsNull(' ' + NullIf(npcr.middle_name, ''), '')
      + IsNull(' ' + NullIf(npcr.last_name, ''), '')
  WHEN 'L' THEN lpcr.name
END

how to call table-valued function in CASE expression

select * from GetLastValueTest('93/.1/01','93/12/29')

GetLastValueTest looks like a table-valued function

then correct way to use it in case statement is:

case 
when v.IsAverage=1 then isnull(avg([Value]),0) 
when v.IsCumulative=1 then   isnull(sum([Value]),0) 
when v.IsCumulative=0 then (select top 1 [ColumnName] from GetLastValueTest('93/.1/01','93/12/29'))
end

Create User defined function with case statement

I think use can create a Scalar-valued Functions

CREATE FUNCTION ConvertStringToDate
(   
    @last_time_received_services NVARCHAR(150)
)
RETURNS DATE
AS
BEGIN
    DECLARE @dateResult DATE
    select @dateResult =
    case 
        when @last_time_received_services like 'Jan-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'01'+ '-' +'01' as date) --1
        when @last_time_received_services like 'Feb-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'02'+ '-' +'01' as date)--2
        when @last_time_received_services like 'Mar-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'03'+ '-' +'01'as date) --3
        when @last_time_received_services like 'Apr-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'04'+ '-' +'01' as date)--4
        when @last_time_received_services like 'May-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'05'+ '-' +'01' as date)--5
        when @last_time_received_services like 'Jun-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'06'+ '-' +'01' as date)--6
        when @last_time_received_services like 'Jul-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'07'+ '-' +'01' as date)--7
        when @last_time_received_services like 'Aug-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'08'+ '-' +'01' as date)--8
        when @last_time_received_services like 'Sep-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'09'+ '-' +'01' as date)--9
        when @last_time_received_services like 'Oct-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'10'+ '-' +'01' as date)--10
        when @last_time_received_services like 'Nov-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'11'+ '-' +'01' as date)--11
        when @last_time_received_services like 'Dec-%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'12'+ '-' +'01' as date)--12
        --Quarterly Payments
        when @last_time_received_services like '%- March%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'03'+ '-' +'01'as date) --3
        when @last_time_received_services like '%- June%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'06'+ '-' +'01' as date)--6
        when @last_time_received_services like '%- September%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'09'+ '-' +'01'as date) --9
        when @last_time_received_services like '%- December%' then  cast(('20' + (right(@last_time_received_services,2))) + '-' +'12'+ '-' +'01' as date)--12
        else ''
    end 

    RETURN @dateResult

END
GO

Use it in your query like

SELECT dbo.ConvertStringToDate(last_time_received_services)
FROM your_table

---

Related Topics