User Defined Functions SQLServer

What are UDF: SQL server provides list of many predefined functions that are built in to the T-SQL language. The supplied functions helps extend the capabilities of T-SQL, providing the ability to perform string manipulation, mathematical calculations data type conversion etc. but often we need something which is not provided using these functions. So we can create stored procedure to perform custom processing, but the problem is that we can’t use the result of stored procedure in WHERE or SELECT list, for this type of scenario we need UDF.
Why to use User Defined Functions: The main benefit of UDF is that we are not just limited to sql provided functions. We can write our own functions to meet our specific needs or to simplify complex SQL codes.

In this Article we will learn about User-Defined Functions (UDFs) in Sql Server. All the examples in this article uses the pubs database.
There are three Types of UDFS in Sql Server:

1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued

Let us go through each of these with examples:
1.  Scalar User-Defined Function
A Scalar UDF can accept 0 to many input parameter and will return a single value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) 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.
Example 1: Here we are creating a Scalar UDF AddTwoNumbers which accepts two input parameters @a and @b and returns output as the sum of the two input parameters.
CREATE FUNCTION AddTwoNumbers
(
@a int,
@b int
)
RETURNS int
AS
BEGIN
RETURN @a + @b
END
Once the above function is created we can use this function as below:
PRINT dbo.AddTwoNumbers(10,20)
OR
SELECT dbo.AddTwoNumbers(30,20)
Note: For Scalar UDFS we need to use Two Part Naming Convention i.e. in the above two statements we are using dbo.AddTwoNumbers.
Whether Below statement is correct? No, because it is not using two-part naming convention. Try executing the below statement it will error out…
PRINT AddTwoNumbers(10,20)
2.  Inline Table-Valued User-Defined Function
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.
Example: In this example we are creating a Inline table-valued function GetAuthorsByState which accepts state as the input parameter and returns firstname and lastname  of all the authors belonging to the input state.
USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS table
AS
RETURN (
SELECT au_fname, au_lname
FROM Authors
WHERE state=@state
)
GO
We can use the below statement to get all the authors in the state CA.
SELECT * FROM GetAuthorsByState(‘CA’)

3. Multi-statement Table-Valued User-Defined Function

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-Sql statement. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, we 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.

Example: In this example we are creating a Multi-Statement Table-Valued function GetAuthorsByState which accepts state as the input parameter and returns author id and firstname of all the authors belonging to the input state. If for the input state there are no authors then this UDF will return a record with no au_id column value and firstname as ‘No Authors Found’.
USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN
INSERT INTO @AuthorsByState
SELECT  au_id,
au_fname
FROM Authors
WHERE state = @state
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState
VALUES (”,’No Authors Found’)
END
RETURN
END
GO
We can use the below statements to get all the authors in the given input state:
SELECT * FROM GetAuthorsByState(‘CA’)
SELECT * FROM GetAuthorsByState(‘XY’)

Other More Examples of UDF
SQL getdate() returns current system date and time. It always includes both data and time components. We want to get just date and have the time always set to midnight. One solution is to to the conversion like below;
select convert(datetime,CONVERT(date,getdate()))
But the problem is that when we want to have date with time always set to midnight, we need to do this conversion. Solution is to make UDF for this.
create function getonlydate()
returns datetime
as
begin
return(select convert(datetime,convert(date,getdate())))
end
go
Now we can call this UDF in our SQL query.
select dbo.getonlydate()
Let us see how we can use this UDF in other SQL statements.
Let us create a table Order
CREATE TABLE Orders (
OrderID int IDENTITY (1, 1) NOT NULL Primary Key,
CustomerID nchar (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
EmployeeID int NULL ,
OrderDate datetime NULL default dbo.getonlydate(),
RequiredDate datetime NULL ,
ShippedDate datetime NULL
)
Let us INSERT values in this table using UDF function we created.
INSERT Orders(CustomerID,EmployeeID,RequiredDate)
values(‘BERGS’,3,dbo.getonlydate() +7)
Let us UPDATE values in this table using UDF function we created.
UPDATE Orders set ShippedDate = dbo.getonlydate()
where OrderID=1
SELECT OrderDate,RequiredDate,ShippedDate
FROM orders
WHERE orderdate = dbo.getonlydate()
Orderdate               Requireddate            Shippeddate
——————————————————————–
2011-05-01 00:00:00.000       2011-05-08 00:00:00.000       2011-05-01 00:00:00.000

Types of User Defined Functions:
1) Scalar functions
2) Inline table valued function
3) Multistatement table valued functions.

A) Scalar Function:
1) They are like standard built in functions provided with SQL Server.
2) It return scalar values that can be used anywhere a constant expression can be used.
3) It typically takes one or more arguments and returns a value of a specified data types.
4) Every T-SQL function must return a result using the RETURN statement.
Example:
The following two functions are variations of a function that returns the average price for a specified type of book from the titles table:
CREATE FUNCTION AverageBookPrice (@booktype varchar(12) = ‘%’)
RETURNS money
AS
BEGIN
DECLARE @Avg money
SELECT @Avg = AVG(price)
FROM titles
WHERE type  like @booktype
RETURN @Avg
END
GO
CREATE FUNCTION   AverageBookPrice2 (@booktype varchar(12) =’%')
RETURNS money
AS
BEGIN
RETURN (SELECT AVG(PRICE)
FROM TITLES
WHERE TYPE LIKE @booktype)
END
## SQL Server doesn’t allow aggregate functions in a WHERE clause unless they are contained in a subquery.
The AvgBookPrice() function lets you compare against the average price without having to use a subquery:
SELECT  title_id, type, price from titles
where price > dbo.AverageBookPrice(‘popular_comp’)
titleid type      price   
———————–
PC1035 popular_comp  22.95
PS1372 psychology    21.59
You can return the value from a user-defined scalar function into a local variable in two ways. You can assign the result to a local variable by using the SET statement or an assignment select, or you can use the EXEC statement. The following commands are functionally equivalent:
declare @avg1 money,
@avg2 money,
@avg3 money
select @avg1 = dbo.AverageBookPrice(‘popular_comp’)
set @avg2 = dbo.AverageBookPrice(‘popular_comp’)
exec @avg3 = dbo.AverageBookPrice ‘popular_comp’
select @avg1 as avg1, @avg2 as avg2, @avg3 as avg3
go
Result is below
avg1     avg2       avg3
———————————–
21.475 21.475 21.475
B) Table Value Function:
1) A table-valued user-defined function returns a rowset instead of a single scalar value.
2) Can be invoked in the FROM clause of a SELECT statement, just as we would a table or view.
3) A table-valued function can almost be thought of as a view that accepts parameters, so the result set is determined dynamically.
4) A table valued function specifies the keyword TABLE in its RETURNS clause.
5) They are of two types.
1) Inline table valued function
A) An inline table-valued function specifies only the TABLE keyword in the RETURNS clause,
Without table definition information.
B) The code inside the function is a single RETURN statement that invokes a SELECT  statement.
Example:
CREATE FUNCTION AveragePriceByType (@price money = 0.0)
RETURNS table
AS
RETURN (SELECT type,avg(isnull(price,0)) as avg_price
FROM titles
GROUP BY type
HAVING avg(isnull(price,0)) > @price )
select * from AveragePriceByType(15.0)
      type        averageprice
—————————————-
trad_cook        15.9633
————————————————————
2) Multi statement table valued function:
a) Multistatement table-valued functions differ from inline functions in two major ways
A) The RETURNS clause specifies a table variable and its definition.
B) The body of the function contains multiple statements, at least one of which populates the table variable with data values.
b) The scope of the table variable is limited to the function in which it is defined.
c) Within the function in which a table variable is defined, that table variable can be treated like a regular table. You can perform any SELECT, INSERT, UPDATE, or DELETE statement on the rows in a table variable, except for SELECT INTO.
The following example defines the inline table-valued function AveragePricebyType() as a multistatement table-valued function called AveragePricebyType3():
CREATE FUNCTION   AveragePricebyType3 (@price money =0.0)
RETURNS @table table(type varchar(12) null,avg_price money null)
AS
BEGIN
INSERT @table
SELECT type,avg(isnull(price,0)) as avg_price
FROM titles
GROUP BY type
HAVING avg(isnull(price,0))> @price
RETURN
END
SELECT * FROM AveragePricebyType3(15.0), this also gives same result.
type        averageprice
—————————————
trad_cook        15.9633
————————————————————
Big Question: Why use multi-statement table-valued functions instead of inline table-valued functions?
1) Generally, we use multi-statement table-valued functions when we need to perform further operations (for example, inserts, updates, or deletes) on the contents of the table variable before returning a result set.
2) We would also use them if we need to perform more complex logic or additional processing on the input parameters of the function before invoking the query to populate the table variable.
Types of SQL statements allowed in a function include the following:

a) DECLARE statements to define variables and cursors that are local to the function.
b) Assignments of values to variables that are local to the function, using the SET command or an assignment select.
c) Cursor operations on local cursors that are declared, opened, closed, and de-allocated within the function. FETCH statements must assign values to local variables by using the INTO clause.
d) Control-of-flow statements such as IF, ELSE, WHILE, GOTO, and so on, excluding the TRY…CATCH statements.
e) UPDATE, INSERT, and DELETE statements that modify table variables defined within the function.
f) EXECUTE statements that call an extended stored procedure. (Any results returned by the extended stored procedure are discarded.)
Nesting of User Defined Function: User-defined functions can also call other user-defined functions, with a limit of 32 levels of nesting. Nesting of functions can help improve the modularity and reusability of function code.
CREATE FUNCTION dbo.getonlydate3()
RETURNS datetime
as
BEGIN
DECLARE @date datetime
SET @date = dbo.striptime( getdate())
RETURN @date
End
How to get information about Functions: To get information by using the provided system procedures and queries against the INFORMATION_SCHEMA.routines view. The following sections describe these methods.

exec sp_helptext getonlydate
Text

create function getonlydate()
returns datetime
as
begin
return(select convert(datetime,convert(date,getdate())))
end
29
In addition to sp_helptext, you can write queries against the INFORMATION_SCHEMA.routines view to display the source code for a function:
SELECT routine_definition
from INFORMATION_SCHEMA.routines
where routine_name = ‘getonlydate’
and specific_schema = ‘dbo’
and specific_catalog = ‘bigpubs2008′
Conclusion: User-defined functions in SQL Server 2008 allow you to create reusable routines that can
Help make your SQL code more straightforward and efficient. Table-valued functions provide a way to create what are essentially parameterized views, and you can include them inline in your queries, just as you would in a table or view.