A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.

Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name.

Stored procedures have long given us the ability to pass parameters and get a value back, but the ability to use it in such a variety of different places where you cannot use a stored procedure make this a very powerful database object. Also notice the logic of my function is not exactly brain surgery. But it does encapsulate the business rules for the different continents in one location in my application. If you were to build this logic into T-SQL statements scattered throughout your application and you suddenly noticed that you forgot a country (like I missed Austria!) you would have to make the change in every T-SQL statement where you had used that logic. Now, with the SQL Server User-Defined Function, you can quickly maintain this logic in just one place.

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Note that the example uses another function (WhichContinent) to select out the customers specified by the parameter of this function. After creating the user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets. Also note that I do not have to reference the dbo in my reference to this function. However, when using SQL Server built-in functions that return a table, you must now add the prefix :: to the name of the function.

Example from Books Online: Select * from ::fn_helpcollations()

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.

So, if you have ever wanted to use the results of a stored procedure as part of a T-SQL command, use parameterized non-updateable views, or encapsulate complex logic into a single database object, the SQL Server 2000 User-Defined function is a new database object that you should examine to see if its right for your particular environment.

Exaples 2:

-- Get the first five authors and book titles.

-- calling a function with constants and expressions

-- Move into the Northwind database to create the next function

-- Drop any previous version of udf_EmpTerritoryCOUNT.

-- This batch isn't in the book. It's provided here in case you've

-- already created the function and want to recreate it.

ELSE

-- These options should be set this way before creating any UDF

-- Create script for udf_EmpTerritoryCOUNT

END

-- EXEC permission is short for EXECUTE

-- Get the territory count for one employee

-- Get 3 the employees with the most territories

-- Drop any previous version of udf_EmpTerritoriesTAB.

-- This batch isn't in the book. It's provided here in case you've

-- already created the function and want to recreate it.

ELSE

-- These options should be set this way before creating any UDF

/*

* Returns a table of information about the territories assigned

* to an employee.

*

* Example:

select * FROM udf_EmpTerritoriesTAB(2)

-- Get the EmployeeID of Andrew Fuller. Use it to get his territories.

-- Now use @EmpID to get the list of territories

-- Still in the Northwind database

-- Get the EmployeeID of Andrew Fuller. Use it to get his regions

-- Multi-statement Tabled Valued UDFs

-- Work in the NorthWind database

-- Drop any previous version of udf_DT_MonthsTAB.

-- This batch isn't in the book. It's provided here in case you've

-- already created the function and want to recreate it.

ELSE

-- Create the udf_DT_MonthsTAB function

/*

* Returns a table of months that are between two dates including

* both end points. Each row has several ways to represent the

* month. The result table is intended to be used in reports that

* are reporting on activity in all months in a range.

*

* Example: -- to create a table of months in 2002

select * FROM dbo.udf_DT_MonBtwnTAB('2002-01-01', '2002-12-31')

-- Get the start of the first month