Look at Table-valued functions in SQL Server 2005

Sateesh Arveti
14y
6.2k
0
0

Blog

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