SQL Ouery Parameter with 2 values-CodePudding

Does anyone know why I'm getting no values from this query when I am trying to give a parameter with two values in it? Like the first parameter? When I'm trying it with only one value it works?

SELECT * FROM amountStatistikFN('1111,2222','2020','10001','1' )

SQL-Function:

ALTER FUNCTION appAmountStatistikFN
(
    @client nvarchar(25),
    @year nvarchar(25),
    @persnr nvarchar(25),--
    @location nvarchar(25)    
)
RETURNS TABLE
AS
RETURN    
(
    SELECT persnr, client, location, activity, year, SUM(amount) AS Summe 
    FROM app_hours 
    WHERE 
    persnr IN (@persnr) AND year=2020 AND location IN (@location) AND client IN (@client)
    GROUP BY persnr, client, location, activity, year       
)
GO

CodePudding user response：

Assuming you want to compare a column against a input CSV string, you could use a LIKE comparison:

ALTER FUNCTION appAmountStatistikFN (
    @client nvarchar(25),
    @year nvarchar(25),
    @persnr nvarchar(25),
    @location nvarchar(25)
)
RETURNS TABLE
AS
RETURN (
    SELECT persnr, client, location, activity, year, SUM(amount) AS Summe 
    FROM app_hours
    WHERE ','   @persnr   ',' LIKE '%,'   persnr   ',%' AND
          year = 2020 AND
          ','   @location   ',' LIKE '%,'   location   ',%' AND
          ','   @client   ',' LIKE '%,'   client   ',%'
    GROUP BY persnr, client, location, activity, year
)
GO

The above answer assumes that the location and client inputs also might be CSV strings. If not, then you may keep the code for those criteria as you originally had it and do a direct comparison of the column against the input parameter.