Creating a scalar function in SQL Server 2012

General:
Consider the following equation:

y = x + x*x + x*x*x
where * is a symbol for multiplication

Here y is a function of x (the value of y depends on the value assigned to x) written symbolically as y=f(x)

A Scalar function in general can depend on more than one variable such as y=f(x,z,t) and return a single value.

How do you create a function in SSMS?

The following function named TestMultiply takes two parameters @param1 and @param2 and returns their product, a single value.

Create function [dbo].[TestMultiply]
( @param1 int,
  @param2 int
)
Returns INT
AS

BEGIN

RETURN @param1 * @param2
END
GO

This is implemented in SQL Server (in SQL Server Management Studio) by opening a query window and typing in the above code and executing the query as shown:


How do we evaluate the function?

First of all we should call the function in a query window by selecting it and providing the two parameters.

Here is how it is evaluated for @param1=5 and @param2=10



How can you create a function that uses values in the database tables?

Let us go to the AdventureWorks2012 database and review the Sales.SalesOrderDetail table. The following shows the structure of the table:

In this table we focus on two columns, UnitPrice and OrderQty. We plan to write a function that multiplies OrderQty with UnitPrice and return a value in the function we call TotalOrder. The function we create is as follows:

Create Function [dbo].[TotalOrder]
(
 @OrderQty int,
 @UnitPrice money
)
Returns MONEY

as

BEGIN
Return @OrderQty * @UnitPrice
END
GO

When you execute the query with the above a scalar function called TotalOrder will be created as shown:



In order to use this function you can call it as shown(shown with some results):

There is a lot more to understand. This just Basic stuff.