Function in sql server 2005

A function is a special type of command word in the SQL99 command set. In effect, functions are one-word commands that return a single value. The value of a function can be determined by input parameters, as with a function that averages a list of database values.

SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.

Functions that take a character string input and return a character string output use the collation of the input string for the output. Functions that take no character inputs and return a character string use the default collation of the current database for the output. Functions that take multiple character string inputs and return a character string use the rules of collation precedence to set the collation of the output string

In this article I am going to explain about some SQL Server 2005 functions with examples. A function performs an operation and returns a value.  A function consists of the function name, followed by a set of parenthesis that contains any parameter or arguments required by the function. If a function requires two or more arguments you can separate them with commas.

Types of Functions 

Aggregate functions

Operate against a collection of values, but return a single, summarizing value.

Scalar functions

Operate against a single value, and return a single value based on the input value. Some scalar functions, CURRENT_TIME for example, do not require any arguments.

Aggregate Functions

Aggregate functions return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement. The supported aggregate functions and their syntax are listed in Table 4-1.

Table 4-1: SQL99 Aggregate Functions

Function	Usage
AVG(expression)	Computes the average value of a column by the expression
COUNT(expression)	Counts the rows defined by the expression
COUNT(*)	Counts all rows in the specified table or view
MIN(expression)	Finds the minimum value in a column by the expression
MAX(expression)	Finds the maximum value in a column by the expression
SUM(expression)	Computes the sum of column values by the expression

User Defined Functions :
A user-defined function is a database object that encapsulates one or more Transact-SQL statements for reuse. This definition is similar to the one for stored procedures, but there are many important differences between user-defined functions and stored procedures—the most pronounced being what types of data they can return.

The following statement shows how to create a function that accepts two input parameters, sums them together and then returns the sum to the calling statement:
 

Three Types of User-Defined Functions

Now that you have seen how easy it is to create and implement a simple function, let’s cover the three different types of user-defined functions and some of the nuances of how they are implemented.

Scalar Functions

A scalar function returns a single value of the data type referenced in the RETURNS clause of the CREATE FUNCTION statement. The returned data can be of any type except text, ntext, image, cursor, or timestamp.
The example we covered in the previous section is a scalar function. Although the previous example only contained one statement in the BEGIN…END block, a scalar function can contain an unlimited number of statements as long as only one value is returned. The following example uses a WHILE construct to demonstrate this.

CREATE FUNCTION fx_SumTwoValues2
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
 WHILE @Val1 <100
  BEGIN
   SET @Val1 =@Val1 +1
  END
  RETURN (@Val1+@Val2)
END
go

SELECT dbo.fx_SumTwoValues2(1,7) AS SumOfTwoValues

SumOfTwoValues
--------------
107

The @Val1 input parameter is set to 1 when the function is called, but the WHILE increments the parameter to 100 before the RETURN statement is executed. Note that the two-part name (owner.object_name) is used to call the function. Scalar functions require that their two-part names be used when they are called. As you will see in the next two sections, this is not the case with the other two types of functions.

Inline Table-Valued Functions

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.
The following uses the Customer table in the Northwind database to show how an inline table-valued function is implemented.

USE Northwind
go
CREATE FUNCTION fx_Customers_ByCity
( @City nvarchar(15) )
RETURNS table
AS
RETURN (
        SELECT CompanyName
        FROM Customers
        WHERE City =@City
   )
go
SELECT * FROM fx_Customers_ByCity('London')

CompanyName
----------------------------------------
Around the Horn
. . . 
Seven Seas Imports

Multi-Statement Table-Valued Functions

The multi-statement table-valued function is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement. Unlike the inline table-valued function, a table variable must be explicitly declared and defined. The following example shows how to implement a multi-statement table-valued function that populates and returns a table variable.

USE Northwind
go
CREATE FUNCTION fx_OrdersByDateRangeAndCount
( @OrderDateStart smalldatetime, 
  @OrderDateEnd smalldatetime, 
  @OrderCount smallint )
RETURNS @OrdersByDateRange TABLE
  (  CustomerID nchar(5),
     CompanyName nvarchar(40),
     OrderCount smallint,
     Ranking char(1) )
AS
BEGIN
 --Statement 1
 INSERT @OrdersByDateRange
 SELECT a.CustomerID,
        a.CompanyName,
        COUNT(a.CustomerID)AS OrderCount,
       'B'
 FROM Customers a
 JOIN Orders b ON a.CustomerID =b.CustomerID
 WHERE OrderDate BETWEEN @OrderDateStart AND @OrderDateEnd
 GROUP BY a.CustomerID,a.CompanyName
 HAVING COUNT(a.CustomerID)>@OrderCount

--Statement 2
UPDATE @OrdersByDateRange
SET Ranking ='A'
WHERE CustomerID IN (SELECT TOP 5 WITH TIES CustomerID
                     FROM (SELECT a.CustomerID,
                                  COUNT(a.CustomerID)AS OrderTotal
                           FROM Customers a
                           JOIN Orders b ON a.CustomerID =b.CustomerID
                           GROUP BY a.CustomerID) AS DerivedTable
                           ORDER BY OrderTotal DESC)
RETURN
END

CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
END