Reader Level:
ARTICLE

How to Create Scalar Value Function in SQL Server 2012

Posted by Vipendra Verma Articles | SQL Server August 27, 2012
In this article I am going to explain how to create scalar value functions in SQL Server 2012.
  • 0
  • 0
  • 15795

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

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:

createtable mcninvoices
(

invoiceid
int notnull identityprimary key,
vendorid
int notnull,
invoiceno
varchar(15),
invoicetotal
money,
paymenttotal
money,
creadittotal
money

)

 Statement that inserts data into a table in SQL Server 2012:

insertinto 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:

SelectStatement.jpg

Statement that create a mcnvendors table in SQL Server 2012:

createtable mcnvendors

 (

 vendoridint,

 vendornamevarchar(15),

 vendorcityvarchar(15),

 vendorstatevarchar(15)

 )

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

insertinto mcnvendors values (20,'vipendra','noida','up')

insertinto mcnvendors values (21,'deepak','lucknow','up')

insertinto mcnvendors values (22,'rahul','kanpur','up')

insertinto mcnvendors values (23,'malay','delhi','delhi')

insertinto mcnvendors values (24,'mayank','noida','up')

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

FetchData.jpg

A Statement that creates a scalar - valued function

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:

CREATEFUNCTION fnbal_invoice()

RETURNSMONEY

BEGIN

 

RETURN(SELECT SUM(invoicetotal-paymenttotal-creadittotal)

        FROM dbo.mcninvoices

               WHEREinvoicetotal-paymenttotal-creadittotal> 0 )

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:

CREATEFUNCTION fnven_info

(@vendoridint )

RETURNSvarchar(15)

BEGIN

    RETURN (SELECTvendorname FROMdbo.mcnvendors

        WHEREvendorid=@vendorid)

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

COMMENT USING

Trending up