How to return the count of records using a user defined function in SQL? Below is the function I wrote but it fails.
CREATE FUNCTION dbo.f_GetRecordCount
(@year INT)
RETURNS INT
AS
BEGIN
RETURN
SELECT COUNT(*)
FROM dbo.employee
WHERE year = @year
END
Perhaps the SELECT query returns TABLE but I need to return INT.
You are returning a table, it's one row containing the count.
If you need to return the value, you need to get that out of your query.
CREATE FUNCTION dbo.f_GetRecordCount(@year INT)
RETURNS INT
AS
BEGIN
DECLARE @returnvalue INT;
SELECT @returnvalue = COUNT(*)
FROM dbo.employee
WHERE year = @year
RETURN(@returnvalue);
END
To add a note of caution though, you should realise that this can have huge performance implications. If you use the UDF in a select-list or a condition, it will be called for every row in the result set - it probably won't be optimised out.
For example this query won't just call the function once, but once for every employee:
SELECT Id, f_GetRecordCount(2001)
FROM employee
RETURN would need parentheses here so that it doesn't think it's just ending the function.
...but don't do it! Scalar functions are bad!
