Create user-defined function (T-SQL)

MS SQL user-defined functions are of 2 types: Scalar and Tabular-Valued based on the type of result set each return.

A Scalar function accepts one or more parameters and returns a single value.

A Table-Valued function takes one input parameter and returns the columns of the table.

Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the functions are being created. [ as referred in the Microsoft Docs ]

Syntax

1. T-SQL Scalar function syntax

Copy
CREATE FUNCTION [ schema_name. ] <function_name> [ (@parameterName datatype)]
RETURNS return_data_type
BEGIN
    function_body --T-SQL statements
    RETURN scalar_expression
END;

2. T-SQL Inline Table-Valued function syntax

Copy
CREATE FUNCTION [ schema_name. ] <function_name> [ (@parameterName datatype)]
RETURNS TABLE
AS
RETURN
(
    T-SQL statements
);
GO

1. Scalar Functions

Copy
USE HASURA;
GO

CREATE FUNCTION dbo.getDeptId (@EmpId int)
RETURNS INT
AS
BEGIN
    DECLARE @DeptId int;
    SELECT @DeptId = e.DeptId
    FROM dbo.EMPLOYEE e
    WHERE e.EmpID=@EmpId;
    RETURN @DeptId;
END;
GO

Invoke the function as:

Copy

SELECT dbo.getDeptId(3);

SELECT e.empName, dbo.getDeptId(e.EmpId) AS DepartmentID FROM EMPLOYEE e;

2. Table-Valued Functions

Copy
CREATE FUNCTION [dbo].[getEmpDetails] (@EmpId int)
RETURNS TABLE
AS
RETURN
(
    SELECT e.EmpName, e.EmpID, d.DeptName
    FROM EMPLOYEE e
    LEFT JOIN DEPARTMENT d
    ON e.DeptId=d.DepartmentId
    WHERE e.EmpId=@EmpId
);
GO

Invoke the function as:

SELECT * FROM dbo.getEmpDetails(4);

result set: