How to use user-Defined Function

CREATE FUNCTION [dbo].[GenerateIntegers] ( @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    DECLARE @Index    INT
    SET @Index = 0
    WHILE @Index <= @MaxValue
    BEGIN
        INSERT INTO @Integers ( [IntValue] ) VALUES ( @Index )
        SET @Index = @Index + 1
    END

    RETURN
END
GO


Description

The user-defined function is quite straight-forward.  It simply inserts into the return TABLE variable (@Integers) the integer values from 1 to the maximum integer value supplied in the parameter using a WHILE loop.  If the value supplied in the parameter is less than 1, then the table returned will not contain any records.

To use this function, you can simply issue a simple SELECT statement and using this in the FROM clause of the SELECT, as follows:

SELECT * FROM [dbo].[GenerateIntegers] ( 1001 )