Some time we need SQL statements those are common to projects, and we need multiple times to execute so in that case we can club reusable sql queries into single unit called functions.

Function in SQL Server are database objects that include a group of SQL statements to perform a specified activity. A function takes parameters, performs actions, and returns the outcome

Why We Need UDFs in SQL Server

Reusability: UDFs allow you to write a piece of logic once and reuse it multiple times across different queries, enhancing code maintainability and reducing duplication.

Modularity: UDFs help in breaking down complex queries into simpler, more manageable pieces of code, promoting modular programming.

Performance Optimization: UDFs can sometimes improve query performance by reducing the need to repeat the same logic across multiple queries.


Types of UDFs in SQL Server

Scalar Functions: Return a single value of any SQL Server data type.

Table-Valued Functions :

  1. Inline TVFs: Return a table data type and are defined using a single SELECT statement.
  2. Multi-Statement TVFs: Return a table and can contain multiple statements to populate the table.


Scalar Functions

Syntax as below in script, it accepts parameters and return value as defined with datatype.

We can call Function like "Select dbo.function as result"


CREATE FUNCTION dbo.fn_CalculateArea (@length DECIMAL(10, 2), @width DECIMAL(5, 2)) RETURNS DECIMAL(10, 2) AS BEGIN RETURN @length*@width END GO //CALLING Function SELECT dbo.fn_CalculateArea(100, 8.25) AS TotalArea;


Inline Table-Valued Function 

Scaler UDF return single value, inline table valued function returns Single row of table, here we will return single select statement.

In below example we are returning employee details based on departmentID

Input Value= Department ID

Return value= Emp details

CREATE FUNCTION dbo.fn_GetEmployeesByDepartment (@departmentId INT) RETURNS TABLE AS RETURN ( SELECT EmployeeID, EmployeeName, DepartmentID FROM Employees WHERE DepartmentID = @departmentId )


We have below list of employees


We have created function and calling UDF as below, here we are getting


Multi-Statement Table-Valued Function

This returns table variable; this table variable must be explicitly declared and defined whose value can be derived from multiple SQL statements.

we can use these type of function at time of joining multiple tables and result of this table will act as table for join

Script

CREATE FUNCTION dbo.fn_GetTotalSalesPerProduct () RETURNS @Sales TABLE ( ProductId INT, TotalSales DECIMAL(10, 2) ) AS BEGIN INSERT INTO @Sales (ProductId, TotalSales) SELECT ProductId, SUM(Quantity * Price) AS TotalSales FROM Sales GROUP BY ProductId; RETURN; END GO


Here in this script we are inserting data into sales variable and returning same

Additional information

UDF can be used for select statement only, we cannot use for Insert, Delete, update.

1. Delete operation: we are getting exception here that we cannot perform this action

2 .Update operation: we are getting exception here that we cannot perform this action

3 . TRY CATCH : We cannot use TRY catch block inside function, we are getting exception below.


To perform complex operation, we can use Stored Procedures

Stored Procedures: More flexible for performing actions, including data modification and administrative tasks. They support complex logic, multiple result sets, output parameters, and transaction control.

line

Copyrights © 2024 letsupdateskills All rights reserved