How to Create Scalar Value Function in SQL
In this article I am going to explain how to create scalar value function in SQL.
Introduction
In SQL Server there are two type of function which are used to manipulate data in database.
- System define function
- User define function
SQL server provide a list of system define function which are used to manipulate data. These system define function are predefine. We can't change functionality of these system define function. There are many situation where we need a function which performer some specific task according to our need. There function are known as user define function.
There are three types of user define function in SQL Server.
- Scalar- value function
- Simple table-valued function
- Multi-statement table-valued function
Scalar- value function
Scalar- value function is used to return a single value of any T-SQL data type.
A Statement that is used to create a mcninvoices table
create table mcninvoices
(
invoiceid int not null identity primary key,
vendorid int not null,
invoiceno varchar(15),
invoicetotal money,
paymenttotal money,
creadittotal money
)
|
A Statement that is used to insert data in mcninvoices table
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)
|
A Statement that show data in mcninvoices table
A Statement that create scalar value function
CREATE FUNCTION fn_bd()
RETURNS MONEY
BEGIN
RETURN ( SELECT SUM(invoicetotal-paymenttotal-creadittotal)
FROM dbo.mcninvoices
WHERE invoicetotal-paymenttotal-creadittotal >0 )
END
|
A Statement that uses scalar value function