Who doesn’t like returning tables from user defined function in SQL? Nobody, that’s who! And fellas, ladies love guys that can do this. So without further ado:
CREATE FUNCTION [dbo].[udfGetCounts] ( @Id int ) RETURNS TABLE AS RETURN ( SELECT COUNT(*) As MyCount, Building FROM tblHistory WHERE FacilityNumber = (SELECT Code FROM tblEntities (NOLOCK) WHERE ID = @Id) GROUP BY Building )
Notice that the function returns a TABLE data type.
To get the results from this function, use the following syntax:
select * from dbo.udfGetCounts(16051)
If you try to call it like a normal function:
Then you will get this message which will cause much weeping and gnashing of teeth:
Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udfGetCounts", or the name is ambiguous.
You can see more information on MSDN.