SQL UDF Group By Parameter Issue
I'm having some issues with a group by clause in SQL. I have the following basic function:
CREATE FUNCTION dbo.fn_GetWinsYear (@Year int)
RETURNS int
AS
BEGIN
declare @W int
select @W = count(1)
from tblGames
where WinLossForfeit = 'W' and datepart(yyyy,Date) = @Year
return @W
END
I'm trying to run the following basic query:
select dbo.fn_GetWinsYear(datepart(yyyy,date))
from tblGames
group by datepart(yyyy,date)
However, I'm encountering the following error message: Column 'tblGames.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Any i开发者_Go百科deas why this is occurring? FYI, I know I can remove the function and combine into one call but I'd like to keep the function in place if possible.
I think you should be calling your function like this.
select dbo.fn_GetWinsYear(datepart(yyyy,getdate()))
OR
select dbo.fn_GetWinsYear('2010')
Essentially you are just passing a year to your function and the function is returning the number of wins for that year.
If you don't know the year, your function could look something like this...
CREATE FUNCTION dbo.fn_GetWinsYear ()
RETURNS @tblResults TABLE
( W INT, Y INT )
AS
BEGIN
INSERT @tblResults
SELECT count(1), datepart(yyyy,[Date])
FROM tblGames
WHERE WinLossForfeit = 'W'
GROUP BY datepart(yyyy,[Date])
RETURN
END
SELECT * FROM dbo.fn_GetWinsYear()
精彩评论