Defining a SQL Server function that returns a table

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:

 select  dbo.udfGetCounts(16051)

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.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: