Function in SQL Server with Examples

Code: Use this below code to select a specific database

USE TestDatabase

Code: Create this below table to understand function

CREATE TABLE [dbo].[Emp_Master](
	[Emp_ID] [nchar](10) NULL,
	[Emp_Name] [nchar](10) NULL,
	[Emp_DOB] [date] NULL
) ON [PRIMARY]
 
 

Code: Create this below table

CREATE TABLE [dbo].[EmpSalary](
	[ID] [nchar](10) NULL,
	[Name] [nchar](10) NULL,
	[Salary] [numeric](10, 2) NULL,
	[Dept] [nchar](10) NULL
) ON [PRIMARY]

Insert some records inside above two table.

Code: use this code to see two table

Select * from EmpSalary
Select * from Emp_Master

ALTER TABLE Emp_Master
ADD Emp_DOB  DATE;

UPDATE Emp_Master SET Emp_DOB = '12-02-1996' WHERE Emp_ID = 1
UPDATE Emp_Master SET Emp_DOB = '11-02-1996' WHERE Emp_ID = 2
UPDATE Emp_Master SET Emp_DOB = '10-02-1996' WHERE Emp_ID = 3
UPDATE Emp_Master SET Emp_DOB = '09-02-1996' WHERE Emp_ID = 4
UPDATE Emp_Master SET Emp_DOB = '08-02-1996' WHERE Emp_ID = 5

Code: Create a simple function using this below code

/* User Defined Function */ 
-- Tabled Valued Function
CREATE FUNCTION SelectDataEmpSalary (
    @ID INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        Name,
        Salary,
        Dept
    FROM
        EmpSalary
    WHERE
        ID = @ID; 

-- Execute Function
SELECT * FROM SelectDataEmpSalary(1)

Code: Use this below code to create a function

/* Scalar Valued Function 1 */
CREATE FUNCTION CubeFunction(@X INT)
RETURNS INT
AS
BEGIN
  RETURN @X * @X *@X
END
-- Execute
SELECT dbo.CubeFunction(5) AS Cube

Code: Example of Scalar Valued function

/* Scalar Valued Function 2 */

 CREATE FUNCTION Addition(@Num1 Decimal(7,2),  
                         @Num2 Decimal(7,2))  
RETURNS Decimal(7,2)  
Begin  
    DECLARE @Result Decimal(7,2)  
    SET @Result = @Num1 + @Num2  
    RETURN @Result  
end  

-- Execute 
print dbo.Addition(12,13) 

Code: Example of Scalar Valued function

/* Scalar Valued Function 3 */
CREATE FUNCTION CalculateAge
(
  @DOB DATE
)
RETURNS INT
AS
BEGIN
  DECLARE @AGE INT
  SET @AGE = DATEDIFF(YEAR, @DOB, GETDATE())-
  CASE
    WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR
       (MONTH(@DOB) = MONTH(GETDATE()) AND
        DAY(@DOB) > DAY(GETDATE()))
    THEN 1
    ELSE 0
  END
  RETURN @AGE
END

-- Execute
SELECT dbo.CalculateAge('12-02-1996') as age


-- Use the above function in a Query
SELECT Emp_ID, Emp_Name, Emp_DOB, dbo.CalculateAge(Emp_DOB) AS Age 
FROM Emp_Master

Code: Example of Scalar Valued function

 /* Drop Function */
 DROP FUNCTION FuncationName

 

Code: Example of Scalar Valued function

/* Advanced Functions */
SELECT USER_NAME();
SELECT SYSTEM_USER;
SELECT SESSION_USER;
SELECT SESSIONPROPERTY('ANSI_NULLS');
SELECT ISNUMERIC(4567);
SELECT NULLIF(25, 25);
SELECT NULLIF('Hello', 'Hello');
SELECT NULLIF('Hello', 'world');
SELECT NULLIF('2017-08-25', '2017-08-25');
SELECT ISNULL(NULL, 'RummanAnsai');
SELECT IIF(500<1000, 'YES', 'NO');
SELECT CONVERT(int, 25.65);
SELECT COALESCE(NULL, NULL, NULL, 'Rumman', NULL, 'Ansari');
SELECT COALESCE(NULL, 1, 2, 'Rumman');
SELECT CAST(25.65 AS int);

/* Date Functions */
SELECT CURRENT_TIMESTAMP;
SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd; /* Add one year to a date, then return the date:*/
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff; /*Return the difference between two date values, in years:*/
SELECT DATEFROMPARTS(2018, 10, 31) AS DateFromParts; /*Return a date from it's parts:*/ 
SELECT DATENAME(year, '2017/08/25') AS DatePartString; /* Return a specified part of a date: */ 
SELECT DATEPART(year, '2017/08/25') AS DatePartInt; /*Return a specified part of a date:*/ 
SELECT DAY('2017/08/25') AS DayOfMonth; /* Return the day of the month for a date: */
SELECT GETDATE();  /* Return the current database system date and time: */
SELECT GETUTCDATE(); /* Return the current UTC date and time: */ 
SELECT ISDATE('2017-08-25'); /* Check if the expression is a valid date: */
SELECT MONTH('2017/08/25') AS Month; /* Return the month part of a date: */ 
SELECT SYSDATETIME() AS SysDateTime; /* Return the date and time of the SQL Server: */ 
SELECT YEAR('2017/08/25') AS Year; /* Return the year part of a date: */