SQL Server User-Defined Functions (UDF)

User-Defined Functions
│
├── 1. Scalar Function          → ek single value return karta hai (int, varchar, date...)
├── 2. Inline TVF (iTVF)        → ek TABLE return karta hai (single SELECT)
└── 3. Multi-Statement TVF      → ek TABLE return karta hai (multiple statements)
    (MSTVF)

CREATE [OR ALTER] FUNCTION [schema_name].[function_name]
(
    @param1 DataType,
    @param2 DataType = DefaultValue   -- optional default
)
RETURNS ReturnType                    -- scalar ke liye datatype, TVF ke liye TABLE
[WITH SCHEMABINDING]                  -- optional: objects se bind karta hai
[WITH RETURNS NULL ON NULL INPUT]     -- optimization hint
AS
BEGIN
    -- logic yahan
    RETURN value_or_table
END

CREATE OR ALTER FUNCTION [dbo].[function_name]
(
    @param DataType
)
RETURNS DataType        -- single value: INT, VARCHAR, DATE, DECIMAL...
[WITH SCHEMABINDING]
AS
BEGIN
    DECLARE @result DataType

    -- logic
    SET @result = ...

    RETURN @result
END

CREATE OR ALTER FUNCTION [dbo].[fnHR_GetAge]
(
    @DateOfBirth DATE
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    /*
        Ye function DOB se current age calculate karta hai
        DATEDIFF sirf year count karta hai, isliye birthday
        check se 1 minus karte hain agar birthday abhi nahi aaya
    */
    DECLARE @Age INT

    SET @Age = DATEDIFF(YEAR, @DateOfBirth, GETDATE())
             - CASE
                   WHEN MONTH(@DateOfBirth) > MONTH(GETDATE()) THEN 1
                   WHEN MONTH(@DateOfBirth) = MONTH(GETDATE())
                    AND DAY(@DateOfBirth)   > DAY(GETDATE())   THEN 1
                   ELSE 0
               END

    RETURN @Age
END
GO

-- USE KARO:
SELECT
    EmployeeID,
    EmployeeName,
    DateOfBirth,
    dbo.fnHR_GetAge(DateOfBirth) AS CurrentAge
FROM Employees;

CREATE OR ALTER FUNCTION [dbo].[fnPAY_IncomeTaxSlab]
(
    @AnnualIncome DECIMAL(18, 2)
)
RETURNS DECIMAL(18, 2)
WITH SCHEMABINDING
AS
BEGIN
    /*
        Old tax regime slabs (simplified example):
        0 - 2.5L    → 0%
        2.5L - 5L   → 5%
        5L - 10L    → 20%
        10L+        → 30%
    */
    DECLARE @Tax DECIMAL(18, 2) = 0

    IF @AnnualIncome <= 250000
        SET @Tax = 0
    ELSE IF @AnnualIncome <= 500000
        SET @Tax = (@AnnualIncome - 250000) * 0.05
    ELSE IF @AnnualIncome <= 1000000
        SET @Tax = (250000 * 0.05) + ((@AnnualIncome - 500000) * 0.20)
    ELSE
        SET @Tax = (250000 * 0.05) + (500000 * 0.20)
              + ((@AnnualIncome - 1000000) * 0.30)

    RETURN @Tax
END
GO

-- USE KARO:
SELECT
    EmployeeID,
    AnnualCTC,
    dbo.fnPAY_IncomeTaxSlab(AnnualCTC)       AS AnnualTax,
    dbo.fnPAY_IncomeTaxSlab(AnnualCTC) / 12  AS MonthlyTDA
FROM EmployeeSalary;

CREATE OR ALTER FUNCTION [dbo].[fnGEN_GetFiscalQuarter]
(
    @Date DATE
)
RETURNS CHAR(6)    -- e.g. 'Q1', 'Q2', 'Q3', 'Q4'
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Month INT = MONTH(@Date)
    DECLARE @Quarter CHAR(2)

    SET @Quarter = CASE
                       WHEN @Month IN (4,  5,  6)  THEN 'Q1'   -- Apr-Jun
                       WHEN @Month IN (7,  8,  9)  THEN 'Q2'   -- Jul-Sep
                       WHEN @Month IN (10, 11, 12) THEN 'Q3'   -- Oct-Dec
                       ELSE                              'Q4'   -- Jan-Mar
                   END

    RETURN @Quarter
END
GO

-- USE KARO:
SELECT
    SaleDate,
    Amount,
    dbo.fnGEN_GetFiscalQuarter(SaleDate) AS FiscalQuarter
FROM SalesData
WHERE dbo.fnGEN_GetFiscalQuarter(SaleDate) = 'Q3';

CREATE OR ALTER FUNCTION [dbo].[fnGEN_RemoveSpecialChars]
(
    @InputString NVARCHAR(500)
)
RETURNS NVARCHAR(500)
WITH SCHEMABINDING
AS
BEGIN
    /*
        Sirf A-Z, a-z, 0-9 aur space rakhna hai
        Baaki sab characters remove karo
        PATINDEX se pehla special char dhundho, STUFF se remove karo
    */
    DECLARE @CleanString NVARCHAR(500) = @InputString
    DECLARE @Pos         INT

    -- Jab tak koi special character mile, hatate raho
    SET @Pos = PATINDEX('%[^A-Za-z0-9 ]%', @CleanString)

    WHILE @Pos > 0
    BEGIN
        SET @CleanString = STUFF(@CleanString, @Pos, 1, '')
        SET @Pos         = PATINDEX('%[^A-Za-z0-9 ]%', @CleanString)
    END

    RETURN LTRIM(RTRIM(@CleanString))
END
GO

-- USE KARO:
SELECT
    RawName,
    dbo.fnGEN_RemoveSpecialChars(RawName) AS CleanName
FROM CustomerImport;

CREATE OR ALTER FUNCTION [dbo].[function_name]
(
    @param DataType
)
RETURNS TABLE
AS
RETURN
(
    -- Sirf ek SELECT statement
    SELECT col1, col2
    FROM   SomeTable
    WHERE  SomeColumn = @param
);
GO

CREATE OR ALTER FUNCTION [dbo].[fnPAY_GetEmployeeSalaryDetail]
(
    @EmployeeID  INT,
    @PayMonth    INT,
    @PayYear     INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.Department,
        s.BasicSalary,
        s.HRA,
        s.Conveyance,
        s.SpecialAllowance,
        s.BasicSalary + s.HRA + s.Conveyance + s.SpecialAllowance AS GrossSalary,
        s.PF + s.ESI + s.TDS                                       AS TotalDeductions,
        (s.BasicSalary + s.HRA + s.Conveyance + s.SpecialAllowance)
            - (s.PF + s.ESI + s.TDS)                              AS NetSalary
    FROM Employees       e
    JOIN EmployeeSalary  s ON s.EmployeeID = e.EmployeeID
    WHERE e.EmployeeID = @EmployeeID
      AND s.PayMonth   = @PayMonth
      AND s.PayYear    = @PayYear
);
GO

-- USE KARO — CROSS APPLY se multiple employees ke liye:
SELECT
    e.EmployeeID,
    sd.*
FROM Employees e
CROSS APPLY dbo.fnPAY_GetEmployeeSalaryDetail(e.EmployeeID, 4, 2025) sd
WHERE e.DepartmentID = 10;

CREATE OR ALTER FUNCTION [dbo].[fnATT_GetAttendanceInRange]
(
    @EmployeeID INT,
    @FromDate   DATE,
    @ToDate     DATE
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        a.AttDate,
        a.InTime,
        a.OutTime,
        a.Status,
        DATEDIFF(MINUTE, a.InTime, a.OutTime) / 60.0 AS WorkingHours
    FROM AttendanceMaster a
    WHERE a.EmployeeID = @EmployeeID
      AND a.AttDate   BETWEEN @FromDate AND @ToDate
      AND a.Status    IN ('P', 'HD')    -- Present ya Half Day
);
GO

-- USE KARO:
SELECT *
FROM dbo.fnATT_GetAttendanceInRange(101, '2025-04-01', '2025-04-30')
WHERE WorkingHours < 8;    -- 8 ghante se kam kaam kiya

CREATE OR ALTER FUNCTION [dbo].[fnGEN_SplitBlank]
(
    @text      NVARCHAR(MAX),
    @delimiter NVARCHAR(20) = ','
)
RETURNS TABLE
AS
RETURN
(
    WITH
    -- Virtual numbers table banao — koi physical table nahi chahiye
    E1(N) AS
    (
        SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),
                             (1),(1),(1),(1),(1)) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),   --       100 rows
    E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b),   --    10,000 rows
    E6(N) AS (SELECT 1 FROM E2 a CROSS JOIN E4 b),   -- 1,000,000 rows

    -- TOP se sirf utne hi rows lenge jitne string ki length hai
    Tally(N) AS
    (
        SELECT TOP (ISNULL(LEN(@text), 0) + 1)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM E6
    ),

    -- Har token ki start position dhundho
    TokenStarts(start_pos) AS
    (
        SELECT 1
        WHERE  @text IS NOT NULL AND LEN(@text) > 0

        UNION ALL

        SELECT N + LEN(@delimiter)
        FROM   Tally
        WHERE  @text IS NOT NULL
          AND  LEN(@delimiter) > 0
          AND  N <= LEN(@text) - LEN(@delimiter) + 1
          AND  SUBSTRING(@text, N, LEN(@delimiter)) = @delimiter
    )

    -- Start position se token extract karo
    SELECT
        SUBSTRING(
            @text,
            start_pos,
            ISNULL(NULLIF(CHARINDEX(@delimiter, @text, start_pos), 0),
                   LEN(@text) + 1) - start_pos
        ) AS value
    FROM TokenStarts
);
GO

-- USE KARO — CSV string ko rows mein convert karo:
SELECT s.value AS SkillName
FROM dbo.fnGEN_SplitBlank('SQL Server,Python,Power BI,Azure', ',') s;
/*
Result:
SQL Server
Python
Power BI
Azure
*/

CREATE OR ALTER FUNCTION [dbo].[function_name]
(
    @param DataType
)
RETURNS @ResultTable TABLE
(
    Col1 DataType,
    Col2 DataType
)
AS
BEGIN
    -- Multiple statements allowed
    INSERT INTO @ResultTable
    SELECT ...

    IF (condition)
    BEGIN
        INSERT INTO @ResultTable ...
    END

    RETURN   -- @ResultTable automatically return ho jaata hai
END
GO

CREATE OR ALTER FUNCTION [dbo].[fnHR_GetEmployeeHierarchy]
(
    @EmployeeID INT
)
RETURNS @Hierarchy TABLE
(
    Level        INT,
    EmployeeID   INT,
    EmployeeName NVARCHAR(200),
    ManagerID    INT,
    Designation  NVARCHAR(100)
)
AS
BEGIN
    /*
        Recursive CTE se employee ka poora reporting chain nikalo
        Level 0 = Employee khud
        Level 1 = Direct Manager
        Level 2 = Manager ka Manager
        ...aur aage
    */
    WITH EmpCTE AS
    (
        -- Base: employee khud
        SELECT
            0               AS Level,
            e.EmployeeID,
            e.EmployeeName,
            e.ManagerID,
            e.Designation
        FROM Employees e
        WHERE e.EmployeeID = @EmployeeID

        UNION ALL

        -- Recursive: manager chain upar jaao
        SELECT
            c.Level + 1,
            e.EmployeeID,
            e.EmployeeName,
            e.ManagerID,
            e.Designation
        FROM Employees e
        JOIN EmpCTE    c ON c.ManagerID = e.EmployeeID
        WHERE c.Level < 10   -- infinite loop se bachao
    )
    INSERT INTO @Hierarchy
    SELECT Level, EmployeeID, EmployeeName, ManagerID, Designation
    FROM   EmpCTE;

    RETURN;
END
GO

-- USE KARO:
SELECT * FROM dbo.fnHR_GetEmployeeHierarchy(1042)
ORDER BY Level;

CREATE OR ALTER FUNCTION [dbo].[fnPAY_GetVariableComponents]
(
    @EmployeeID INT,
    @Month      INT,
    @Year       INT
)
RETURNS @Components TABLE
(
    ComponentName  NVARCHAR(100),
    ComponentType  CHAR(1),       -- 'E' = Earning, 'D' = Deduction
    Amount         DECIMAL(18,2)
)
AS
BEGIN
    -- Earnings insert karo
    INSERT INTO @Components
    SELECT ComponentName, 'E', Amount
    FROM   PayrollEarnings
    WHERE  EmployeeID = @EmployeeID
      AND  PayMonth   = @Month
      AND  PayYear    = @Year;

    -- Deductions insert karo
    INSERT INTO @Components
    SELECT ComponentName, 'D', Amount
    FROM   PayrollDeductions
    WHERE  EmployeeID = @EmployeeID
      AND  PayMonth   = @Month
      AND  PayYear    = @Year;

    -- Agar koi row nahi aaya toh default row daalo
    IF NOT EXISTS (SELECT 1 FROM @Components)
    BEGIN
        INSERT INTO @Components VALUES ('No Data', 'E', 0);
    END

    RETURN;
END
GO

CREATE OR ALTER FUNCTION [dbo].[fnHR_GetAge]
(
    @DOB DATE
)
RETURNS INT
WITH SCHEMABINDING    -- ← Ye wala
AS
BEGIN
    ...
END

-- Har employee ke liye last 3 months ki attendance summary
SELECT
    e.EmployeeID,
    e.EmployeeName,
    att.AttDate,
    att.WorkingHours
FROM Employees e
CROSS APPLY
(
    SELECT TOP 3
        a.AttDate,
        DATEDIFF(MINUTE, a.InTime, a.OutTime) / 60.0 AS WorkingHours
    FROM dbo.fnATT_GetAttendanceInRange(
             e.EmployeeID,
             DATEADD(MONTH, -3, GETDATE()),
             GETDATE()
         ) a
    ORDER BY a.AttDate DESC
) att;

-- Sirf vo employees jinka age 30+ ho
SELECT EmployeeID, EmployeeName
FROM   Employees
WHERE  dbo.fnHR_GetAge(DateOfBirth) >= 30;

-- ⚠️ WARNING: Scalar function in WHERE → Index use nahi hota!
-- Better approach: Computed column ya calling query mein calculate karo

-- Table mein Age automatically calculate karo
ALTER TABLE Employees
ADD Age AS dbo.fnHR_GetAge(DateOfBirth);   -- virtual, stored nahi

-- Ya persisted (stored):
ALTER TABLE Employees
ADD Age AS dbo.fnHR_GetAge(DateOfBirth) PERSISTED;
-- Persisted ke liye function DETERMINISTIC hona chahiye (SCHEMABINDING required)

-- Custom validation: joining date future mein nahi ho sakti
CREATE OR ALTER FUNCTION [dbo].[fnHR_ValidateJoiningDate]
(
    @JoiningDate DATE
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN @JoiningDate <= GETDATE() THEN 1 ELSE 0 END
END
GO

ALTER TABLE Employees
ADD CONSTRAINT CK_ValidJoiningDate
    CHECK (dbo.fnHR_ValidateJoiningDate(JoiningDate) = 1);

RBAR = Row By Agonizing Row

-- 10 lakh rows? → Function 10 lakh baar chalega!
SELECT EmployeeID, dbo.fnPAY_IncomeTaxSlab(AnnualCTC) AS Tax
FROM   EmployeeSalary;   -- 1,000,000 rows = 1,000,000 function calls

-- Scalar (slow):
SELECT dbo.fnPAY_IncomeTaxSlab(AnnualCTC) FROM EmployeeSalary;

-- iTVF equivalent (fast):
CREATE OR ALTER FUNCTION [dbo].[fnPAY_TaxSlabITVF]
(
    @AnnualIncome DECIMAL(18,2)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        CASE
            WHEN @AnnualIncome <= 250000  THEN 0
            WHEN @AnnualIncome <= 500000  THEN (@AnnualIncome - 250000)  * 0.05
            WHEN @AnnualIncome <= 1000000 THEN 12500 + (@AnnualIncome - 500000)  * 0.20
            ELSE                               112500 + (@AnnualIncome - 1000000) * 0.30
        END AS Tax
);
GO

-- Use with CROSS APPLY:
SELECT e.EmployeeID, t.Tax
FROM   EmployeeSalary e
CROSS APPLY dbo.fnPAY_TaxSlabITVF(e.AnnualCTC) t;

-- Check karo kya tera function inlineable hai:
SELECT
    name,
    is_inlineable,        -- 1 = Yes, 0 = No
    inline_type
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.fnPAY_IncomeTaxSlab');

-- Function deterministic hai ya nahi check karo:
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnHR_GetAge'), 'IsDeterministic');
-- 1 = Deterministic (same input → always same output)
-- 0 = Non-deterministic

-- Deterministic functions indexed computed columns mein use ho sakte hain!

-- MSTVF ke saath execution plan dekho:
-- Estimated rows = 1 (hamesha!)
-- Actual rows = 5000
-- → Bad join strategy, bad memory grant → SLOW!

-- Fix: Use OPTION (RECOMPILE) ya iTVF mein convert karo
SELECT *
FROM   SomeTable st
JOIN   dbo.fnHR_GetEmployeeHierarchy(101) h ON h.EmployeeID = st.EmployeeID
OPTION (RECOMPILE);    -- har baar fresh plan banao

-- Ek payroll slip query jisme 3 UDFs use ho rahe hain
SELECT
    e.EmployeeID,
    e.EmployeeName,
    e.Department,
    dbo.fnGEN_GetFiscalQuarter(s.PayDate)       AS FiscalQuarter,
    s.GrossSalary,
    dbo.fnPAY_IncomeTaxSlab(s.AnnualCTC)  / 12  AS MonthlyTax,
    dbo.fnHR_GetAge(e.DateOfBirth)               AS EmployeeAge,
    s.GrossSalary
        - (dbo.fnPAY_IncomeTaxSlab(s.AnnualCTC) / 12)
        - s.PF - s.ESI                           AS NetTakeHome
FROM Employees      e
JOIN EmployeeSalary s ON s.EmployeeID = e.EmployeeID
WHERE s.PayMonth = MONTH(GETDATE())
  AND s.PayYear  = YEAR(GETDATE());

-- SP ke andar iTVF string splitter use karo
CREATE OR ALTER PROCEDURE [dbo].[uspRPT_GetMultiEmployeeReport]
    @EmployeeIDList NVARCHAR(MAX)    -- '101,102,103,104'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT e.*
    FROM   Employees e
    WHERE  e.EmployeeID IN
    (
        SELECT CAST(value AS INT)
        FROM   dbo.fnGEN_SplitBlank(@EmployeeIDList, ',')
        WHERE  value <> ''
    );
END
GO

-- Call karo:
EXEC dbo.uspRPT_GetMultiEmployeeReport '101,102,103,104,105';

-- Bulk import se pehle data validate karo
CREATE OR ALTER FUNCTION [dbo].[fnGEN_IsValidEmail]
(
    @Email NVARCHAR(200)
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE
               WHEN @Email LIKE '%_@_%.__%'
                AND @Email NOT LIKE '%[<>()[\]\\,;: ]%'
               THEN CAST(1 AS BIT)
               ELSE CAST(0 AS BIT)
           END
END
GO

-- Import table mein invalid emails dhundho:
SELECT *
FROM CustomerImport
WHERE dbo.fnGEN_IsValidEmail(EmailAddress) = 0;

-- ERROR: Cannot perform alter on incompatible object type
-- (MSTVF → iTVF convert karte time)

-- FIX: DROP → CREATE
DROP FUNCTION IF EXISTS dbo.fnGEN_SplitBlank;  -- SQL 2016+
GO
CREATE FUNCTION dbo.fnGEN_SplitBlank...
GO

-- Scalar function NULL return kar sakta hai agar NULL input aaye
SELECT dbo.fnHR_GetAge(NULL);   -- NULL return hoga, error nahi

-- FIX: NULL handle karo function ke andar:
IF @DateOfBirth IS NULL RETURN NULL;
-- Ya function option:
CREATE FUNCTION ... RETURNS INT
WITH RETURNS NULL ON NULL INPUT   -- Auto NULL return karo

-- SQL Server mein recursive function ki max depth = 32
-- Zyada deep recursion → Error 217

-- FIX: Level counter se limit lagao:
WHERE c.Level < 10   -- ya apni zaroorat ke hisaab se

-- YE KAAM NAHI KARTA:
CREATE FUNCTION dbo.fnTest(@d DATE)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(DAY, @d, GETDATE())   -- ERROR! GETDATE() non-deterministic
END

-- FIX: SCHEMABINDING hatao ya GETDATE() ko parameter se pass karo:
CREATE FUNCTION dbo.fnTest(@d DATE, @Today DATE)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(DAY, @d, @Today)
END

-- YE KAAM NAHI KARTA SCALAR/iTVF MEIN:
CREATE FUNCTION dbo.fnTest(@x INT)
RETURNS INT AS
BEGIN
    INSERT INTO AuditLog VALUES (@x, GETDATE())   -- ERROR!
    RETURN @x
END

-- FIX: Agar DML chahiye toh Stored Procedure use karo, Function nahi

fn{Module}_{Action}{Object}

fn = Function prefix (always)
Module = PAY, HR, ATT, GEN, RPT
Action = Get, Calc, Validate, Convert, Split
Object = Employee, Date, Salary, String

Examples:
✅ fnPAY_GetNetSalary
✅ fnHR_CalcAge
✅ fnGEN_SplitBlank
✅ fnATT_GetWorkingDays
❌ GetSalary (no fn prefix)
❌ fn1 (meaningless)

□ Scalar UDF hai aur large table pe use hoga → iTVF mein convert karo
□ SCHEMABINDING lagaya hai?
□ WITH RETURNS NULL ON NULL INPUT hai NULL-passthrough logic ke liye?
□ MSTVF use kar rahe ho → kya iTVF mein convert ho sakta hai?
□ SQL Server 2019+ hai → is_inlineable check kiya?
□ WHERE clause mein scalar UDF → Index use nahi hoga, computed column consider karo
□ Recursive function mein level limit lagayi hai?

-- Sirf execute permission do, table access mat do directly:
GRANT EXECUTE ON dbo.fnPAY_IncomeTaxSlab TO [ReportingRole];

-- Schema-level security:
GRANT SELECT ON SCHEMA::dbo TO [ReadOnlyUser];

Kya chahiye?                        → Kaunsa UDF?
─────────────────────────────────────────────────
Single value (age, tax, quarter)    → Scalar UDF
                                      (small tables ke liye theek)

Set-based filtering, joins,         → Inline TVF ⭐
large tables, CROSS APPLY           → ALWAYS prefer this

Complex row-by-row logic,           → MSTVF
hierarchy, conditional inserts      (last resort)