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

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 ( invoiceidint notnull identityprimary key, vendoridint notnull, invoicenovarchar(15), invoicetotalmoney, paymenttotalmoney, creadittotalmoney )

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

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

Statement that shows all data of mcninvoicetable in SQL Server 2012:

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:

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

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:

CREATEFUNCTION fnven_info (@vendoridint ) RETURNSvarchar(15) BEGIN     RETURN (SELECTvendorname FROMdbo.mcnvendors         WHEREvendorid=@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: