09
JunGrab Deal : Flat 20% off on live classes - SCHEDULE CALL
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.
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.
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 |
These are functions that are already defined in the system.
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. |
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
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
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.
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 are functions that are developed by the user.
Read: Learn SQL Union All Query Operators with Examples
Scalar Function | Table Valued Functions |
User-defined function that returns a single value | User-defined functions that returns more than one value |
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
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 |
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
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
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 |
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.
I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.
AWS
DevOps
Data Science
Hadoop
Salesforce
QA
Business Analyst
MS SQL Server
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews