Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL User-Defined Functions (UDFs)
User-Defined Functions (UDFs) in SQL allow you to create custom functions that can perform operations and return results. These functions can be scalar, returning a single value, or table-valued, returning a table that can be queried like a regular table.
Purpose
- Modularity: Encapsulate repetitive or complex logic into reusable functions.
- Readability: Improve the readability of queries by abstracting calculations and logic.
- Reusability: Enable consistent use of logic across different queries and procedures.
- Flexibility: Allow custom computations tailored to specific needs.
Types of UDFs
- Scalar Functions: Return a single value, such as a number or string.
- Table-Valued Functions (TVFs): Return a table that can be used in queries like a regular table.
Syntax
Creating a Scalar Function:
CREATE FUNCTION function_name (parameter_name data_type)
RETURNS data_type
AS
BEGIN
— Function logic
RETURN result;
END;
Creating a Table-Valued Function:
CREATE FUNCTION function_name (parameter_name data_type)
RETURNS TABLE
AS
RETURN
(
— SQL query returning a table
);
Example:
1. Scalar Function to Calculate Bonus
Function Definition:
CREATE FUNCTION CalculateBonus (Salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
RETURN Salary * 0.10;
END;
Using the Function in a Query:
SELECT employee_id, first_name, salary,
dbo.CalculateBonus(salary) AS bonus
FROM employees;
Output:
employee_id | first_name | salary | bonus |
1 | Rajesh | 55000 | 5500 |
2 | Priya | 48000 | 4800 |
2. Table-Valued Function to Get Employees by Department
Function Definition:
CREATE FUNCTION GetEmployeesByDepartment (DeptID INT)
RETURNS TABLE
AS
RETURN
(
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = DeptID
);
Using the Function in a Query:
SELECT * FROM dbo.GetEmployeesByDepartment(1);
Output:
employee_id | first_name | last_name | salary |
1 | Rajesh | Kumar | 55000 |
2 | Priya | Sharma | 48000 |
3. Scalar Function to Format Full Name
Function Definition:
CREATE FUNCTION FormatFullName (FirstName VARCHAR(50), LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
RETURN CONCAT(FirstName, ‘ ‘, LastName);
END;
Using the Function in a Query:
SELECT employee_id, dbo.FormatFullName(first_name, last_name) AS full_name
FROM employees;
Output:
employee_id | full_name |
1 | Rajesh Kumar |
2 | Priya Sharma |
4. Table-Valued Function to Get Orders Within a Date Range
Function Definition:
CREATE FUNCTION GetOrdersByDateRange (StartDate DATE, EndDate DATE)
RETURNS TABLE
AS
RETURN
(
SELECT order_id, order_date, order_amount
FROM orders
WHERE order_date BETWEEN StartDate AND EndDate
);
Using the Function in a Query:
sql
Copy code
SELECT * FROM dbo.GetOrdersByDateRange(‘2024-01-01’, ‘2024-08-31’);
Output:
order_id | order_date | order_amount |
101 | 2024-02-15 | 500 |
102 | 2024-06-10 | 1500 |
Tips to Remember
- Ensure Correct Data Types: Make sure parameter data types match the expected input to avoid errors.
- Handle Errors: Implement error handling within functions to manage potential issues.
- Optimize Performance: Monitor and optimize UDFs for performance, particularly for complex or large table-valued functions.
- Security: Use UDFs to manage sensitive calculations and prevent direct access to underlying data.
Frequently Asked Questions
Q1: Can scalar functions be used in SELECT statements?
A1: Yes, scalar functions can be used in SELECT statements to perform calculations and transformations on data.
Example:
SELECT employee_id, first_name, salary, dbo.CalculateBonus(salary) AS bonus
FROM employees;
Q2: Can table-valued functions be used in JOIN operations?
A2: Yes, table-valued functions can be used in JOIN operations just like regular tables.
Example:
SELECT e.employee_id, e.first_name, o.order_id
FROM employees e
JOIN dbo.GetOrdersByDateRange(‘2024-01-01’, ‘2024-08-31’) o
ON e.employee_id = o.employee_id;
Q3: Can UDFs call other functions or procedures?
A3: Yes, UDFs can call other functions but not stored procedures.
Q4: What is the difference between scalar and table-valued functions?
A4: Scalar functions return a single value, while table-valued functions return a table that can be queried like a regular table.
Q5: Can UDFs be used in WHERE clauses?
A5: Yes, scalar functions can be used in WHERE clauses to filter results based on custom calculations.
Example:
SELECT * FROM employees
WHERE dbo.CalculateBonus(salary) > 5000;
