Tuesday 17 February 2009

Returning tables from TSQL Functions

I was just blown away when I saw that you could return a table from a TSQL function. It's just something I've not seen before despite working with TSQL for years.

Heres a sample function :


CREATE FUNCTION [dbo].[FNHTest]()
RETURNS @MyTbl TABLE(ID INT NOT NULL, Keyword varchar(max))
AS
BEGIN
INSERT INTO @myTbl (ID,Keyword)
SELECT CompanyID, CompanyName FROM Company
RETURN
END


...and calling it ...

select * from dbo.FNHTest()


... I wonder if this is how Views are created and used internally by SQL?