How to Create Scalar Value Function in SQL Server 2012

In this article I am going to explain how to create scalar value functions in SQL Server 2012.

Introduction

 
In this article, I am going to explain how to create scalar value functions in SQL Server 2012. In SQL Server 2012 there are two types of functions to manipulate data:
  • System define function
  • User-defined function
To manipulate data SQL Server 2012 provides many system-defined functions. These system-defined functions (or built-in functions) are predefined. We can't change the functionality of these built-in functions in SQL Server 2012. There are many situations where we need a function which performs a custom task according to our specific needs. Those functions are known as user-defined functions.
 
SQL Server 2012 provides three types of user-defined functions
  • Scalar- valued function
  • Simple table-valued function
  • Multi-statement table-valued function

Scalar value function in SQL Server

 
A Scalar-valued function in SQL Server 2012 is used to return a single value of any T-SQL data type.  A CREATE FUNCTION statement is used to create a Scalar-valued function. The name of the function should not be more than 128 characters. It is not a rule but it is conventional that the name of the function should begin with the prefix fn.
 
Up to 1024 input parameters can be defined for Scalar-valued functions.  A Scalar-valued function however cannot contain an output parameter. The value is returned by a Scalar-valued function using the RETURNS clause.
 
Statement that creates a table in SQL Server 2012
  1. create table mcninvoices  
  2. (  
  3.     invoiceid int not null identity primary key,  
  4.     vendorid int notnull,  
  5.     invoiceno varchar(15),  
  6.     invoicetotal money,  
  7.     paymenttotal money,  
  8.     creadittotal money  
  9. )  
Statement that inserts data into a table in SQL Server 2012
  1. insert into mcninvoices values (20,'e001',100,100,0.00)  
  2. insert into mcninvoices values (21,'e002',200,200,0.00)  
  3. insert into mcninvoices values (22,'e003',500,0.00,100)  
  4. insert into mcninvoices values (23,'e004',1000,100,100)  
  5. insert into mcninvoices values (24,'e005',1200,200,500)  
  6. insert into mcninvoices values (20,'e007',150,100,0.00)  
  7. insert into mcninvoices values (21,'e008',800,200,0.00)  
  8. insert into mcninvoices values (22,'e009',900,0.00,100)  
  9. insert into mcninvoices values (23,'e010',6000,100,100)  
  10. insert into mcninvoices values (24,'e011',8200,200,500)  
Statement that shows all data of mcninvoicetable in SQL Server 2012
 
SelectStatement.jpg 
 
Statement that creates a mcnvendors table in SQL Server 2012
  1. create table mcnvendors  
  2. (  
  3.     vendorid int,  
  4.     vendorname varchar(15),  
  5.     vendorcity varchar(15),  
  6.     vendorstate varchar(15)  
  7. )  
Statements that inserts data in the mcnvendors table in SQL Server 2012
  1. insert into mcnvendors values (20,'vipendra','noida','up')  
  2. insert into mcnvendors values (21,'deepak','lucknow','up')  
  3. insert into mcnvendors values (22,'rahul','kanpur','up')  
  4. insert into mcnvendors values (23,'malay','delhi','delhi')  
  5. insert into mcnvendors values (24,'mayank','noida','up')  
A Statement that is used to fetch data from the mcnvendors table in SQL Server 2012
 
FetchData.jpg 
 

Creating a scalar valued function in SQL Server

 
Here we create a user-defined (Scalar-valued function) which calculates the amount of a company. It is a simple function and doesn't need a parameter; see:
  1. CREATE FUNCTION fnbal_invoice()  
  2. RETURNS MONEY  
  3. BEGIN  
  4. RETURN(SELECT SUM(invoicetotal-paymenttotal-creadittotal)  
  5.         FROM dbo.mcninvoices  
  6.         WHERE invoicetotal-paymenttotal-creadittotal > 0 )  
  7. END
createFunctioninSQLServer.jpg
 
A Statement that invokes the scalar valued function
 
In this statement we use a function to fetch data from a table. 
 
UseFunction.jpg 
 
A Statement that creates another scalar valued function
 
Here we create a user-defined (Scalar-valued function) which finds the name of a vendor using vendorid. It is a simple function that does not need a parameter; see:
  1. CREATE FUNCTION fnven_info  
  2. (@vendorid int )  
  3. RETURNS varchar(15)  
  4. BEGIN  
  5.     RETURN (SELECT vendorname FROM dbo.mcnvendors  
  6.         WHERE vendorid=@vendorid)  
  7. END
DatabaseFunction.jpg
 
A Statement that invokes another scalar valued function
 
In this statement we use a function to find all records of an employee through vendorid; see:
 
ParameterisedFunctioninSQL.jpg 
 
UseParameterisedFunction.jpg 
 
usedatabsefunction.jpg