Different Type Of SQL Functions And Their Uses

Imagine a scenario such as this. You are writing a complex query where you need to add up a series of values. How would you do it?

One way of doing it is as below.

Select val1 + val2 + val3 from <Table Name>.

But this particular approach has a drawback. Today you might have five values and you have framed your query based on that. What will happen when tomorrow the number of value increases? Would you again get inside the query and change? The above query may look simple. But what if you have a complex query where you have written the above type of statement more than once.

Another approach is to use functions. SQL Server has a lot of built-in functions already defined in the system. You can use them if they serve your purpose. For example in case of the above scenario you can use the SUM() function and frame your query like below :

Select sum(Val) from <Table Name>.

Even if the already provided system-defined functions does not serve your purpose, SQL Server also allows developers to write their functions.

Over the next few paragraphs, we will learn about different types of SQL Functions.

What is SQL Function?

SQL Functions are programs either developed by the user or already provided by the SQL Server system which can be used to perform certain repetitive tasks.

Different type of SQL Functions

System Defined Function	User-Defined Function
This function is defined by SQL Server.	Any SQL functions developed by the user
Three types of functions	Any functions developed by a user

System defined functions

These are functions that are already defined in the system.

Different types of System Defined Functions

Aggregate Functions	String Functions	Date Functions	Advanced Functions
These are system defined functions which deal with numbers	These are system defined functions which deals with strings	These are system defined functions which deals with date.	These are system defined functions which performs certain complex task like logical conditions etc
Sum(), AVG(), MAX(), MIN(), COUNT() etc. are some example	LTRIM(), RTRIM(), LEN(), LEFT(), RIGHT(), LOWER() etc. are some of the example	GETDATE(), DATEADD(), DAY(), MONTH(), YEAR() etc. are some of the example	IIF(), CAST(), CONVERT(), CURRENT_USER(), ISNUMERIC() etc. are some examples.

Aggregate Functions example

We will be using SalesOrderDetail of Adventureworks database for the below examples.

Sum()

Select sum (OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This sums up the OrderQty column value of SalesOrderDetail table.

 The output is

Avg()

select avg(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives the average of OrderQty column of SalesOrderDetail table.

Max()

select max(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives out the maximum value of the OrderQty column of [SalesOrderDetail] table.

Min()

select min(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives out the minimum value of the OrderQty column of [SalesOrderDetail] table.

Count()

Read: How to Insert Multiple Rows Using Stored Procedure in SQL?

Select count (*) from [Person]. [Person]

Returns total number of records in the Person table

String Function Example

LTRIM()

Removes space from left side of the string

select ltrim(' tes')

RTRIM()

Removes space from left side of the string

select Rtrim('tes  ')

Len()

Gives the length of the string.

select Len('Test')

LEFT()

select Left('Sanchayan',3)

This SQL Statement extracts three characters from the left side of the string.

RIGHT()

select Right('Sanchayan',3)

The above SQL Statement extracts three characters from the right side of the string

Examples of Date function

GETDATE()

Gives out the current date

select GETDATE()

DATEADD()

SELECT DATEADD (month, 1, '20060830');

Add a month with the date value 20060830

DAY()

select day('12/18/2019')

The SQL Statement gives the current day value of the date passed as parameter.

Read: How to Become a Database Administrator? Just Know the 5 Steps

MONTH()

select MONTH('12/18/2019')

The SQL Statement gives the current month value of the date passed as parameter.

YEAR()

select YEAR('12/18/2019')

The SQL Statement gives the current year value of the date passed as parameter.

Advance Function example

IIF()

Can be used for if else condition in a single select statement.

The following query gives out MALE if gender is male and FEMALE otherwise.

select JobTitle,iif(Gender='M','MALE','FEMALE') [GENDER] from [HumanResources].[Employee]

CAST()

SELECT CAST(25.65 AS int)

This converts the value 25.65 into integer.

The output is

CONVERT()

Converts a string into a different data type here integer.

SELECT CONVERT(int, 25.65);

The output is as below

CURRENT_USER

This advance function gives out the current user of the system

select CURRENT_USER

ISNUMERIC()

This function checks whether the parameter passed in it is numeric or not.

select ISNUMERIC(5)

This gives out 1 if true and 0 if false.

User defined Functions

User-defined functions are functions that are developed by the user.

Read: Learn SQL Union All Query Operators with Examples

Different types of User-Defined Functions

Scalar Function	Table Valued Functions
User-defined function that returns a single value	User-defined functions that returns more than one value

Scalar Functions Example

The following code is a simple function which accepts two integer values and returns the sum of the two integers.

CREATE FUNCTION Func_Add_
(
@val1   int,
@val2   int
)
RETURNS int
BEGIN
Return @val1 + @val2;
END

To execute the function we need to run the following command.

select [dbo].[Func_Add_] (2,3)

The output is

Different types of table valued function

Inline table-valued function	Multi statement table-valued function
Returns a table object as output	Returns a table variable as output
Includes only one select statement	Include multiple statement
The processing time is faster	The processing time is slower

Inline table valued function example

We will be using the SalesOrderHeader table of Adventureworks database. This particular function accepts a date as parameter and gives out the details of all the sales order details on that particular date.

CREATE FUNCTION func_inlinetablelevel (

@order_date date

)

RETURNS TABLE

AS

RETURN

select * from [Sales].[SalesOrderHeader] where

OrderDate = @order_date;

To run the function we need to use the following statement

select * from func_inlinetablelevel('2011-05-31')

The output is

Multi statement table valued function

This particular query accepts a sales order id and returns the total quantity sold against the order.

create FUNCTION tablemultivaluedfunctioneg  (@Parameters int)

RETURNS @FunctionResultTableVariable TABLE (N int)

AS

BEGIN

INSERT INTO @FunctionResultTableVariable

SELECT OrderQty from [Sales].[SalesOrderDetail]

where [email protected] ;

RETURN;

END

GO

WE can run the function using the following statement

select * from tablemultivaluedfunctioneg(43659)

The output is

Different between Functions and Procedures

Stored Procedure	Functions
Compiled only once and executed again and again	Compiled every time before execution
It is optional to return a value	Function always returns a value
Cannot be called from a function	Can be called from a stored procedure
Cannot call procedure within a procedure	Can call a function from within a function

Calling a function within a function

A function can be called from within a function. In this paragraph we will learn how to call a function from within a function.

Here in this example we will call the get date function from within the year function.

Let us see how it works.

Here goes the query statement

select year(getdate())

Here is the output

Few more complicated function example

The first one is to create a Fibonacci number series using functions.We will pass the number of rows as parameter.

Fibonaccci number series as we all know looks like this

0

1

1

2

3

5

8

13

Now let us see how the code looks like

CREATE FUNCTION fn_Fibonacci(@max int)
RETURNS @numbers TABLE(number int)
AS
BEGIN
        	Declare @n1 int = 0,@n2 int =1,@i int=0,@temp int
        	Insert Into @numbers Values(@n1),(@n2)
        	WHILE (@i<[email protected])
        	BEGIN
                    	Insert Into @numbers Values(@[email protected])
                    	set @temp = @n2
                    	Set @n2 = @n2 + @n1
                    	Set @n1 = @temp
                    	Set @i += 1
        	END	
        	RETURN
END

To execute the function we need to write

select * from [dbo].[fn_Fibonacci] (15)

The output looks like below

Summary

In the above write-up, we have tried to explain, some of the important aspects of SQL Functions. This is not a comprehensive study but will give the reader a fair bit of idea about the different types of SQL Functions and their uses. This introductory write-up about the SQL Blogs will make the readers interested in learning more about this part of the SQL server that is called functions.

FaceBook Twitter Google+ LinkedIn Pinterest Email