SQL Server User-Defined Functions

Types of UDF

1. Scalar Functions
2. Table-Valued Functions

For example, take a look at the following Student and Subject tables.

Scalar Functions

Generally, a Scalar UDF accepts a single parameter and returns a single value. There are no restriction on the return type of a scalar function, except that it cannot return text, text, images, cursors, or timestamps. SQL queries can use scalar functions in the WHERE clause.

Creating Scalar Function

Scalar functions are created using the following syntax.

CREATE FUNCTION  function-name (Parameters)  
RETURNS  return-type  
AS  
BEGIN  
    Statement 1  
    Statement 2  
             .  
             .  
    Statement n  
    RETURN return-value  
END 

Example

Here is a function you can create.
CREATE FUNCTION GetStudent(@Rno INT)  
 RETURNS VARCHAR(50)  
 AS  
BEGIN  
    RETURN (SELECT Name FROM Student WHERE Rno=@Rno)  
END 

The following command can be used to run this function.

PRINT dbo.GetStudent(1)

Output

Ram

Table-Valued Functions

There are no parameters to a Table-Valued UDF, and it returns a table variable. Querying the results of a join with other tables is possible with this type of function since it returns a table. There are two types of Table-Valued Functions: an "Inline Table-Valued Function" and a "Multi-Statement Table Valued Function".

A. Inline Table-Valued Function

SELECT statements are required in Inline Table-Valued Functions. The return value of the function is the result of the query. An Inline function does not require a BEGIN-END block.

Creating Inline Table-Valued Function

Inline Table-Valued functions are created using the following syntax.

CREATE FUNCTION function-name (Parameters)  
RETURNS return-type  
AS  
RETURN 

Example

Here is a function you can create.

CREATE FUNCTION GetAllStudents(@Mark INT)  
RETURNS TABLE  
AS  
RETURN  
    SELECT *FROM Student WHERE Marks>=@Mark 

The following command can be used to run this function.

SELECT *FROM GetAllStudents(60) 

Output

B. Multi-Statement Table Valued Function

A Multi-Statement contains more than one SQL statement enclosed in a BEGIN and END block. Data can be read from databases and some operations can be performed in the function body. The return value of a Multi-Statement Table Valued Function is declared as a table variable, including the entire structure of the table. It returns the declared table variable without a value in the RETURN statement.

Creating Multi-Statement Table Valued Function

Multi-Statement Table-Valued functions are created using the following syntax.

CREATE FUNCTION  function-name (Parameters)  
RETURNS @TableName TABLE  
(Column_1 datatype,  
    .  
    .  
 Column_n datatype  
)  
AS  
BEGIN  
Statement 1  
        Statement 2  
              .  
              .  
        Statement n  
        RETURN   
        END 

Example

Here is a function you can create.

CREATE FUNCTION GetAvg(@Name varchar(50))  
RETURNS @Marks TABLE  
(Name VARCHAR(50),  
 Subject1 INT,   
         Subject2 INT,   
 Subject3 INT,   
 Average DECIMAL(4,2)  
)  
AS  
BEGIN  
            DECLARE @Avg DECIMAL(4,2)  
            DECLARE @Rno INT       
            INSERT INTO @Marks (Name)VALUES(@Name)       
            SELECT @Rno=Rno FROM Student WHERE Name=@Name  
SELECT @Avg=(Subject1+Subject2+Subject3)/3 FROM Subjects WHERE Rno=@Rno  
      
            UPDATE @Marks SET   
Subject1=(SELECT Subject1 FROM Subjects WHERE Rno=@Rno),  
            Subject2=(SELECT Subject2 FROM Subjects WHERE Rno=@Rno),  
            Subject3=(SELECT Subject3 FROM Subjects WHERE Rno=@Rno),  
            Average=@Avg  
            WHERE Name=@Name  
RETURN   
END 

The following command can be used to run this function.

SELECT * FROM GetAvg('Ram') 

Output

Also Read - TCL Commands In SQL

Frequently Asked Questions

How does a SQL Server UDF work?

User-defined functions are programming constructs in SQL Server that allow parameters to be passed in, do work with those parameters, and return results. 

SQL Server has a number of different types of user-defined functions. What are they?

There are three types of user-defined functions (UDFs) in SQL Server: scalar-valued, table-valued, and multi-statement table-valued.

What is the purpose of UDF in SQL?

The cost of compiling T-SQL code is reduced with UDFs since they cache and reuse plans for repeated execution. The traffic on the network can be reduced. You can use UDFs to filter data based on complex constraints. 

How are stored procedures and user-defined functions different?

It is only possible to read select statements with the user-defined function, while DML statements are not allowed.

Is it possible to use temporary tables within a user-defined function?

Users can use Table Variables in User Defined Functions instead of Temporary Tables.  

Conclusion

In this blog, we learned about SQL Server user-defined functions and its different types. We also learned why it is beneficial to use user-defined functions in SQL.

Check out this link if you want to learn SQL.

Refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, JavaScript, System Design and many more!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!!