Returning Table from functions in SQL server 2000

There are two ways by which one can return a table data type from a UDF in SQL server 2000. Here we will see the examples of both methods.

Returning a table variable.

This example loops through a comma seperated list of values passed to it and inserts these values in a table variable which is returned.

CREATE FUNCTION dbo.fnGetIDList(@strList VARCHAR(8000))
RETURNS @tblList TABLE ([ID] INT PRIMARY KEY)
AS

BEGIN

DECLARE @intID AS INT DECLARE @CommaPosition INT
SET @intID = 0 IF (RIGHT(@strList, 1) <> ',')
SET @strList = @strList + ','

WHILE LEN(@strList) <> 0
BEGIN
SET @CommaPosition = CHARINDEX(',', @strList)
SET @intID = SUBSTRING(@strList, 0, @CommaPosition)
SET @strList = SUBSTRING(@strList, @CommaPosition + 1, LEN(@strList) - @CommaPosition + 1)
INSERT @tblList VALUES (@intID)
END
RETURN
END

Returning TABLE data type.
CREATE Function dbo.fnGetUserInfo (@UserName as Varchar(20))
RETURNS TABLE
AS
RETURN
(
select Status,Name,CreateDate,Password from sysUsers where Name=@UserName
)
GO

If you can return the table using a single query, it is better from performance point of view to use the second method.