Case in where using Parameter-CodePudding

I would appreatiate an advice. I need to filter table valued function on parameter. If Parametr is '', it should return all items. If parameter is not '', it should apply filter.

create FUNCTION [dbo].[items] (@ItemGroupCode varchar(10)) 
RETURNS TABLE
AS
RETURN
(

select * from items

left join ItemGroup on items.Iditemgroup = ItemGroup.Id

where 
case when ItemGroup.Code<>'' then ItemGroup.Code>=@ItemGroupCode end

)

This code does not work, query results on incorrect syntax on >=. I would appreciate any advice. Thank you!

CodePudding user response：

Modify the WHERE clause like this

WHERE ItemGroup.Code >=
CASE 
WHEN @ItemGroupCode<>'' THEN @ItemGroupCode 
ELSE
0 -- The minimum value of ItemGroup.Code
END

Supposing that the minimum value of ItemGroup.Code is >=0

CodePudding user response：

In your case, and generally, its much better to use AND OR operators to achieve your goal in WHERE clause.

You can modify your code like below:

CREATE FUNCTION [dbo].[items] (@ItemGroupCode varchar(10)) 
RETURNS TABLE
AS
RETURN (
    SELECT * 
    FROM [items]
    LEFT JOIN 
        [ItemGroup] 
    ON
        [items].[Iditemgroup] = [ItemGroup].[Id]
    WHERE
        [ItemGroup].[Code] = '' 
        OR [ItemGroup].[Code] >= @ItemGroupCode
)

Hope that'll answer your question.