:::: MENU ::::

SQL Server T-SQL user defined function (UDF)

  • Jan 07 / 2010
  • 0
dbDigger, User Defined Functions UDF

SQL Server T-SQL user defined function (UDF)

User Defined Function (UDF) became available in SQL Server 2000. User Defined Function (UDF) allow you to create routines that execute much like built-in functions, but they perform data manipulations specific to your business needs. For example, you could write a function that calculates the number of business days between two dates. Avoid their confusion with stored procedures and views. There are some thing common between them but all three are used in different scenarios and when used optimally none can take over other.
Query execution plan of User Defined Function (UDF) may be cached and perform well. So optimization and re use is provided.
As you will build and use the User Defined Function (UDF) you will have a good comprehension and will be able to compare these with SP and views, and will be able to choose the best that fit. So lets get in.
In T-SQL User Defined Function (UDF) fall under three categories.

  • Scalar
  • Inline table-valued
  • Multi-statement table-valued

In coming lines we will go through brief description and use of these three categories.

SCALAR UDF:

A scalar user-defined function is used to return a single value based on zero or more parameters. Like a SP you may provide it one or more variables and after defined processes, will return a single value. Scalar User Defined Function (UDF) are often used for converting or translating a current value to a new value, or performing other sophisticated lookups based on specific parameters. Scalar functions can be used within search, column, and join expressions.
Consider following piece of code to create a scalar UDF in pubs that will convert whole column of pubs database to upper case (for learning).

 
CREATE FUNCTION testScalar (@input varchar(50))  
 RETURNS varchar(50)  
 as  
 BEGIN  
 DECLARE @output varchar(50)  
 SET @output = UPPER(@input)  
 RETURN @output  
 END  
 GO  

Now to test the created function

 SELECT dbo.testscalar(au_lname)  
 FROM pubs..authors  
 GO  

INLINE TABLE-VALUED UDF:

Inline table-valued UDF returns a table data type. Result is based on single select statement that is used to define the result. You do not explicitly define the returned table, but use a single SELECT statement for defining the returned rows and columns.
The plus point is that unlike SP a User Defined Function (UDF) can be referenced in from clause of a query. It may be used to create joins to other tables. And I have to mention that unlike view they can accept parameters.
Consider the following piece of code to create an inline User Defined Function (UDF) that will return all rows from sales table with quantity greater than given parameter.

 
USE pubs  
 GO  
 CREATE FUNCTION  
 testInLine (@input INT)  
 RETURNS TABLE  
 AS  
 RETURN(SELECT * FROM SALES  
 WHERE QTY > @input)  
 GO  

Now use the function to simply count the returned rows

 
SELECT COUNT(*)  
 FROM testinline(2)  
 GO  

MULTI-STATEMENT TABLE-VALUED:

Like inline table-valued UDF a multi-statement table valued UDF also returns a result set and is referenced in from clause. But the difference is that these are not restricted to use simple select statement for data retrieval within function definition. But these can use multiple T-SQL statements to retrieve the required result.
Consider following piece of code to create and run a multi-statement UDF. The code is from sqlteam.com. I liked this brief example so its better to consider it here.

 
CREATE FUNCTION dbo.customersbycountry ( @Country VARCHAR(15) )  
 RETURNS  
 @CustomersbyCountryTab TABLE (  
 [CustomerID] [NCHAR] (5), [CompanyName] [NVARCHAR] (40),  
 [ContactName] [NVARCHAR] (30), [ContactTitle] [NVARCHAR] (30),  
 [Address] [NVARCHAR] (60), [City] [NVARCHAR] (15),  
 [PostalCode] [NVARCHAR] (10), [Country] [NVARCHAR] (15),  
 [Phone] [NVARCHAR] (24), [Fax] [NVARCHAR] (24)  
 )  
 AS  
 BEGIN  
 INSERT INTO @CustomersByCountryTab  
 SELECT [CustomerID],  
 [CompanyName],[ContactName],[ContactTitle],  
 [Address],[City],[PostalCode],[Country],  
 [Phone],[Fax]  
 FROM [Northwind].[dbo].[Customers]  
 WHERE country = @Country  
 DECLARE @cnt INT  
 SELECT @cnt = COUNT(*)  
 FROM @customersbyCountryTab  
 IF @cnt = 0  
 INSERT INTO @CustomersByCountryTab (  
 [CustomerID],[CompanyName],[ContactName],  
 [ContactTitle],[Address],[City],  
 [PostalCode],[Country],[Phone],  
 [Fax] )  
 VALUES ('','No Companies Found','','','','','','','','')  
 RETURN  
 END  
 GO  
 SELECT * FROM dbo.customersbycountry('USA')  
 SELECT * FROM dbo.customersbycountry('CANADA')  
 SELECT * FROM dbo.customersbycountry('ADF')  

Further

like any T-SQL object you may alter or drop a UDF. And here is a use full code snippet to find all UDFs in any database.

 
SELECT * FROM [dbName]..sysobjects  
 WHERE xtype = 'FN'  
 GO  

Just replace [dbname] with name of your database.
At the end here are some important attributes of UDF.

  • User Defined Function (UDF) prohibit Usage of Non-Deterministic Built-in Functions, Functions GETDATE(), RAND() etc can not be used in User Defined Function (UDF).
  • User Defined Function (UDF) Returns Only One Result Set or Output Parameter. Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
  • User Defined Function (UDF) can not Call Stored Procedure. Only access to Extended Stored Procedure.
  • User Defined Function (UDF) can not run dynamic SQL which are dynamically build in User Defined Function (UDF). Temporary Tables can not be used in UDF as well.
  • User Defined Function (UDF) can not Return XML. FOR XML is not allowed in UDFUser Defined Function (UDF) does not support SET options. SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
  • User Defined Function (UDF) does not Support Error Handling. RAISEERROR or @@ERROR are not allowed.
  • You can use a User Defined Function (UDF) directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can’t use a stored procedure in a SELECT statement.
  • User Defined Function (UDF) cannot be used for DML operations (INSERT/UPDATE/DELETE.
  • You can not use print statement inside a scalar User Defined Function (UDF)
Consult us to explore the Databases. Contact us