Using function in T-SQL

Categories: Database; Tagged with: ; @ November 2nd, 2012 16:49

Basic function:


ALTER FUNCTION fnGetAge(@birthDate datetime) RETURNS int
AS   
    BEGIN
        -- RETURN MONTH(@birthDate)
        RETURN DateDiff(day, @birthDate, getdate())/365.25
    END

SELECT dbo.fnGetAge('09/10/1985');

Table-Valued Functions:

--INLINE Table-Valued Functions, returns a result set, like a view
CREATE FUNCTION fnGetStusByScore(@score int) RETURNS TABLE
AS   
    RETURN SELECT * FROM UserDavid WHERE score = @score

SELECT * FROM fnGetStusByScore(61)

Multi-Statement Table-Valued Functions:

-- Multi-Statement Table-Valued Functions, returns a table-type result set, the table is explicitly constructed in the script.

ALTER FUNCTION fnGetUsers(@score int)
    RETURNS @userList TABLE
        (UserID int, UserName varchar(32))
    BEGIN
        IF @score IS NULL
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid
            END
        ELSE
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid WHERE score > @score
            END
    RETURN
    END

SELECT * FROM fnGetUsers()

<->



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.