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