Actually, there are two types of table valued functions. The first one is simple. It allows exactly one select statement, nothing more. A lot like a view but it can accept parameters
-- =============================================
-- Table value function
-- =============================================
--1/1/1900 create
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_')
DROP FUNCTION ddf_
GO
CREATE FUNCTION ddf_ ()
-- highlight and run this line to test the function
--select * from ddf_()
Returns table
AS
return
select '001' as Warehouse
go
The second is more complicated. You have to specify the table structure, but you can use any number of SQL statements that you need.
-- =============================================
-- Table value function
-- =============================================
--1/1/1900 create
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_UPR30300_SUM_Weeks')
DROP FUNCTION ddf_UPR30300_SUM_Weeks
GO
CREATE FUNCTION ddf_UPR30300_SUM_Weeks (@myParameter int)
--select * from ddf_UPR30300_SUM_Weeks(2)
Returns @myTable table (col1 int)
AS
begin
declare @a int
insert into @myTable (col1) values (1)
insert into @myTable (col1) values (@myParameter)
return
end
go