Monday, January 26, 2015

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.

Get started with latest release of Microsoft SQL Operations Studio

Read my previous articles (9) on previous versions. Download the February (latest) release of Microsoft SQL Operations Studio here . T...