How to Create Scalar Value Function 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

create table mcninvoices
(
    invoiceid int not null identity primary key,
    vendorid int notnull,
    invoiceno varchar(15),
    invoicetotal money,
    paymenttotal money,
    creadittotal money
)

Statement that inserts data into a table in SQL Server 2012

insert into mcninvoices values (20,'e001',100,100,0.00)
insert into mcninvoices values (21,'e002',200,200,0.00)
insert into mcninvoices values (22,'e003',500,0.00,100)
insert into mcninvoices values (23,'e004',1000,100,100)
insert into mcninvoices values (24,'e005',1200,200,500)
insert into mcninvoices values (20,'e007',150,100,0.00)
insert into mcninvoices values (21,'e008',800,200,0.00)
insert into mcninvoices values (22,'e009',900,0.00,100)
insert into mcninvoices values (23,'e010',6000,100,100)
insert into mcninvoices values (24,'e011',8200,200,500)

Statement that shows all data of mcninvoicetable in SQL Server 2012

 

 

 

Statement that creates a mcnvendors table in SQL Server 2012

create table mcnvendors
(
    vendorid int,
    vendorname varchar(15),
    vendorcity varchar(15),
    vendorstate varchar(15)
)

Statements that inserts data in the mcnvendors table in SQL Server 2012

insert into mcnvendors values (20,'vipendra','noida','up')
insert into mcnvendors values (21,'deepak','lucknow','up')
insert into mcnvendors values (22,'rahul','kanpur','up')
insert into mcnvendors values (23,'malay','delhi','delhi')
insert into mcnvendors values (24,'mayank','noida','up')

A Statement that is used to fetch data from the mcnvendors table in SQL Server 2012

 

 

 

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:

CREATE FUNCTION fnbal_invoice()
RETURNS MONEY
BEGIN
RETURN(SELECT SUM(invoicetotal-paymenttotal-creadittotal)
        FROM dbo.mcninvoices
        WHERE invoicetotal-paymenttotal-creadittotal > 0 )
END

 

A Statement that invokes the scalar valued function

 

In this statement we use a function to fetch data from a table. 

 

 

 

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:

CREATE FUNCTION fnven_info
(@vendorid int )
RETURNS varchar(15)
BEGIN
    RETURN (SELECT vendorname FROM dbo.mcnvendors
        WHERE vendorid=@vendorid)
END

 

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: