SQL Ouery Parameter with 2 values

Question

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

`persnr IN (@persnr)` searches for `persnr` equal to the entire string '1111,2222'. — Serg, Jan 20 '22 at 07:10
Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Charlieface, Jan 20 '22 at 11:02
Although I suggest you either change the setup of the function so that you can pass in a Table Valued Parameter, or you `CROSS APPLY` the function against a table of IDs — Charlieface, Jan 20 '22 at 11:03

Tim Biegeleisen · Accepted Answer · 2022-01-20T08:05:51.977

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.

Thanks for the answer but FIND_IN_SET didn't work. I'm working with ms sql. — Nino Stursa, Jan 20 '22 at 07:46
@NinoStursa Fro some reason I thought you were using MySQL. I have edited my answer such that it should work for SQL Server now. — Tim Biegeleisen, Jan 20 '22 at 08:06

SQL Ouery Parameter with 2 values

1 Answers1