Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Benefits of UDF
3.
Types of UDF
3.1.
Scalar Functions
3.2.
Table-Valued Functions
3.2.1.
A. Inline Table-Valued Function
3.2.2.
B. Multi-Statement Table Valued Function
4.
Frequently Asked Questions
4.1.
How does a SQL Server UDF work?
4.2.
SQL Server has a number of different types of user-defined functions. What are they?
4.3.
What is the purpose of UDF in SQL?
4.4.
How are stored procedures and user-defined functions different?
4.5.
Is it possible to use temporary tables within a user-defined function?
5.
Conclusion
Last Updated: Mar 27, 2024
Medium

SQL Server User-Defined Functions

Author vishal teotia
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

User-Defined Functions (UDFs) are available in SQL Server as well as programming languages. The UDF feature was introduced in SQL Server 2000. UDFs are programming constructs that accept parameters, perform actions, and return results. Scalar values or result sets are returned as the result. A UDF can be used in a script, stored procedure, trigger, and other UDFs within a database.

Benefits of UDF

  1. Modular programming is supported by UDFs. UDFs can be called at any times as you like after you create them and store them in a database. Modifying the UDF doesn't require modifying the source code.
  2. With UDFs, you can cache T-SQL plans and reuse them for repeated executions, which reduces compilation time and costs.
  3. Network traffic can be reduced by using them. UDFs can be used to filter data according to complex constraints. In a WHERE clause, you can use this UDF to filter data.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Types of UDF

  1. Scalar Functions
  2. Table-Valued Functions


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

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

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

Table

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 AlgorithmsCompetitive ProgrammingJavaScriptSystem Design and many more!

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

Happy Learning!!

Previous article
Sql Server Stored Procedures
Next article
MS SQL Server Triggers
Live masterclass