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 :
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.
Copyrights © 2024 letsupdateskills All rights reserved