User defined function

A user-defined function ( english function user-defined , acronym UDF ) in a programming environment refers to a function that will create and the user himself can integrate into its projects. It fulfills a task like a macro , but is implemented via a function call. User-defined functions are available in general programming languages such as Visual Objects , scripting languages such as PHP , special macro software such as AutoIt and in database languages such as SQL and Firebird .

The syntax of a user-defined function must correspond to the syntax of the underlying programming language , and predefined standard functions and other user-defined functions can be used in the definition. A user-defined function must provide exactly one return value.

Unlike a stored procedure , a user-defined function cannot be started as a program .

Examples

Scalar user-defined function in SQL

The following scalar user-defined function is passed the foreign key @PersNr of the person of data type Integer . The function returns the lowest lecture number for this person.

CREATE FUNCTION GibMinVorlesungsnummer(@PersNr INT)
RETURNS INT
AS
RETURN
AS
(
    RETURN
    SELECT MIN(VorlNr)
    FROM Vorlesung
    WHERE PersNr = @PersNr
);

After this user-defined function is stored in the database , it can be used within a SQL query. The query

SELECT Professor.PersNr, Professor.Name, GibMinVorlesungsnummer(PersNr) AS MinVorlNr,
    (SELECT Titel
    FROM Vorlesung
    WHERE VorlNr = GibMinVorlesungsnummer(PersNr)
    ) AS Titel
FROM Professor;

could return the following table as a result:

PersNo	Surname	MinVorl	title
12	Wirth	1000	Software development 1
15th	Tesla	1001	Databases
20th	Vacationers	1200	Networks 1

External Table Function in SQL

In this variant, exactly one unnamed SQL table is supplied as the return value. It can be anywhere within an SQL statement where an SQL table can be. A control entry that refers to an external program is defined at the SQL level. The database system usually supports several programming languages in which the program can be written.

The following user-defined function in SQL has two parameters @VorlNr , @PersNr of the data type Integer :

CREATE FUNCTION GibVorlesungen(@VorlNr INT, @PersNr INT)
RETURNS TABLE
AS
RETURN
AS
(
    SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
    FROM Professor INNER JOIN Vorlesung
    ON Professor.PersNr = Vorlesung.PersNr
    WHERE VorlNr = @VorlNr AND PersNr = @PersNr
);

The query

SELECT * FROM GibVorlesungen(1001, 15);

could return the following result (see SQL view ):

Template no	title	PersNo	Surname
1001	Databases	15th	Vacationers

Web links

• Microsoft Docs: [ https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-ver15

Create User-defined Functions (Database Engine)]