How to Create Scalar Value Function in SQL

In this article I am going to explain how to create scalar value function in SQL.
  • 3125

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

 inv111.jpg

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

Clipboard04.jpg


Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.