有誰知道為什么當我試圖給出一個包含兩個值的引數時，我沒有從這個查詢中得到任何值？像第一個引數一樣？當我只用一個值嘗試它時它有效嗎？
SELECT * FROM amountStatistikFN('1111,2222','2020','10001','1' )
SQL函式：
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
uj5u.com熱心網友回復：
假設您要將列與輸入 CSV 字串進行比較，您可以使用LIKE比較：
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
上面的答案假設位置和客戶端輸入也可能是 CSV 字串。如果沒有，那么您可以將這些標準的代碼保留為您最初擁有的代碼，并將列與輸入引數進行直接比較。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/416746.html
標籤：