Creating Scalar functions in SQL

November 04, 2014 , , , 0 Comments

In earlier article we have discussed about user defined functions that have the limited scope in sql programming in compare to stored procedures. User defined function have two types i.e. scalar function and table-valued function. In this article we will discuss about scalar functions.

Scalar function accept a single parameter and return a single data value of the type specified in the RETURNS clause. A scalar function can return any data type except text, ntext, image, cursor, and timestamp. Some scalar functions, such and current_timestamp, do not require any arguments.

A function contains a series of T-SQL statements defined in a BEGIN…END block of the function body that returns a single value.

Consider an example of a scalar function that calculates the monthly salary of the employees accepting the pay rate as an input and returning a single value after multiplying the value with the number of hours and number of days:

CREATE FUNCTION HumanResources.MonthlySal (@PayRate float)
RETURN float
RETURN (@PayRate * 8 * 30)

You can execute this function by using the following statements:

DECLARE @PayRate float
SET @PayRate = HumanResources.MonthlySal (12.25)
PRINT @PayRate

In the preceding code, @PayRate is a variable that will store a value returned by the MonthlySal function.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google