Creating Scalar-valued Functions in SQL Server Management Studio

We use functions in T-SQL to return a value based on passed parameters just like we do on any other language. But in SQL one use of functions is to provide in-line data processing in our queries like you will see in our example today.

Begin by expanding the database in your SQL Management Studio, then Programmability, Functions, and right-clicking on Scalar-valued Functions and then select "New Scalar-valued Function...".

Notice Table-valued functions? We won't cover it for now as those kind of functions return a whole table like Stored Procedures. Maybe we'll do that in another lesson. But right now let's focus on Scalar-valued functions which will return a single value.

From the template, you may delete the comments if you want.

Our sample function will be returning a string value (varchar in SQL) of the month depending on the passed integer, which can range from 1-12. Let's call our function GetMonthName.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetMonthName]
(
    -- Add the parameters for the function here
    @month int
)

RETURNS varchar(10)
AS
BEGIN
    DECLARE @month_name varchar(10)

    IF @month = 1
    BEGIN
        SET @month_name = 'January';
    END
    ELSE IF @month = 2
    BEGIN
        SET @month_name = 'February';
    END
    ELSE IF @month = 3
    BEGIN
        SET @month_name = 'March';
    END
    ELSE IF @month = 4
    BEGIN
        SET @month_name = 'April';
    END
    ELSE IF @month = 5
    BEGIN
        SET @month_name = 'May';
    END
    ELSE IF @month = 6
    BEGIN
        SET @month_name = 'June';
    END
    ELSE IF @month = 7
    BEGIN
        SET @month_name = 'July';
    END
    ELSE IF @month = 8
    BEGIN
        SET @month_name = 'August';
    END
    ELSE IF @month = 9
    BEGIN
        SET @month_name = 'September';
    END
    ELSE IF @month = 10
    BEGIN
        SET @month_name = 'October';
    END
    ELSE IF @month = 11
    BEGIN
        SET @month_name = 'November';
    END
    ELSE IF @month = 12
    BEGIN
        SET @month_name = 'December';
    END
    ELSE
    BEGIN
        SET @month_name = 'Invalid month number!';
    END


    RETURN @month_name;
END

So to explain, we use the CREATE FUNCTION keyword to create our function followed by our desired name then the parameters (name and type) enclosed in parentheses. Then the RETURNS keyword followed by its type which in our case is a varchar. It's just to say that our function must return a varchar. After that follows our logic which is just a basic if..else.. that will return the corresponding month name or invalid if it is not between 1 to 12. Then of course return that string for our varchar return value.

Here's sample output from our scalar-valued function:

In our 4th example we use the built-in SQL function getdate() to get the current system date then enclose it with another built-in function month() which accepts datetime as parameter and returns the month in integer form, which our function accepts as parameter and finally returns the name of the month.

'Til our next lesson... I hoped you learned/remembered something today.

-k