Functions in SQL Server

There are two types of function available in SQL Server.

1. Scalar Functions

CREATE FUNCTION [dbo].[GetWorkingDays] 
( 
    @startDate SMALLDATETIME, 
    @endDate SMALLDATETIME 
)
RETURNS INT 
AS 
BEGIN
    DECLARE @range INT;

    SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;

    RETURN 
    (
        SELECT 
            @range / 7 * 6 + @range % 7 - 
            (
                SELECT COUNT(*) 
            FROM
                (
                    SELECT 1 AS d
                    UNION ALL SELECT 2 
                    UNION ALL SELECT 3 
                    UNION ALL SELECT 4 
                    UNION ALL SELECT 5 
                    UNION ALL SELECT 6 
                    UNION ALL SELECT 7
                ) weekdays
                WHERE d <= @range % 7 
                AND DATENAME(WEEKDAY, @endDate - d + 1) 
                IN
                (
                  ---  'Saturday',
                    'Sunday'
                )

)
    );
END 

2. Table-Valued  Functions

CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MaxValue INT )
RETURNS @IntegerValues TABLE ( [IValue] INT )
AS
BEGIN
    DECLARE @Index    INT
    SET @Index = 1
    WHILE @Index <= @MaxValue
    BEGIN
        INSERT INTO @IntegerValues ( [IValue] ) VALUES ( @Index )
        SET @Index = @Index + 1
    END

    RETURN
END
GO

To run this function on query prompt

SELECT * FROM [dbo].[ufn_GenerateIntegers] ( 250 )