Look at Table-valued functions in SQL Server 2005

Table-valued functions helps us to return a table datatype and can be used as an alternative to views. We can refer the table returned by the function in FROM clause of select statement.

It's syntax:

 
CREATE
FUNCTION dbo.GetInfo(@CustomerID int)
returns
@userTable table -- Defines format of the table to be returned.
(

ID
int not null,
name
nvarchar(20) not null
)

AS
BEGIN
DECLARE

@ID
int,
@CName
nvarchar(20)
SELECT
@ID= id, @CName = name from myTable where id=@CustomerID;
INSERT
@userTable
SELECT
@ID, @CName;
RETURN
;-- It cannot have any arguments. Once it is called, returns all rows in table variable
END
;
--Procedure to call it
GO


SELECT ID,name from dbo.GetInfo(100);
GO