:::: MENU ::::

Posts Categorized / User Defined Functions 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.


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)  
 DECLARE @output varchar(50)  
 SET @output = UPPER(@input)  
 RETURN @output  

Now to test the created function

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


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  
 testInLine (@input INT)  
 WHERE QTY > @input)  

Now use the function to simply count the returned rows

 FROM testinline(2)  


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) )  
 @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)  
 INSERT INTO @CustomersByCountryTab  
 SELECT [CustomerID],  
 FROM [Northwind].[dbo].[Customers]  
 WHERE country = @Country  
 SELECT @cnt = COUNT(*)  
 FROM @customersbyCountryTab  
 IF @cnt = 0  
 INSERT INTO @CustomersByCountryTab (  
 [Fax] )  
 VALUES ('','No Companies Found','','','','','','','','')  
 SELECT * FROM dbo.customersbycountry('USA')  
 SELECT * FROM dbo.customersbycountry('CANADA')  
 SELECT * FROM dbo.customersbycountry('ADF')  


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'  

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)
  • Apr 10 / 2009
  • 0
dbDigger, Null Values, T-SQL Tips and Tricks, User Defined Functions UDF

Use ISNULL to replace NULL value with alternate

It may be required to change NULL value of a column with a valid alternate value. This scenario is mostly there for report generation purpose. T-SQL provide a very handy function ISNULL() to replace NULL value with alternate value. Following is syntax for using ISNULL()
ISNULL(ColumntoCheckForNULL, AlternateValue)
ISNULL accepts two parameters. First parameter will always be the column for which alternate is required in case of its NULL value. Second parameter is the alternate value that is required to replace the NULL value. Second value may be any other column or other value. Alternate value may be implicitly converted to data type of first parameter. For example you may provide datetime as alternate value for character family data type but you can not provide character family data type as alternate of date time value.
For example if we are required to display City as alternate of NULL value of AddressLine2

 USE AdventureWorks  
 SELECT AddressLine1, ISNULL(AddressLine2,city), PostalCode  
 FROM Person.address  

Similarly to display a string of characters as alternate of NULL AddressLine2

 USE AdventureWorks  
 SELECT AddressLine1, ISNULL(AddressLine2,'No Address Line 2'), PostalCode  
 FROM Person.address  

This user defined function would be very helpful while working with string concatenations. If one of the strings being concatenated is NULL then end result of concatenation would be NULL. To avoid this situation use ISNULL where NULL value is expected.Just consider following two examples for use and effect of ISNULL in string concatenation.

-- String concatenation with NULL value  
 SELECT 'Value1 '+'Value2 '+NULL AS ConcatenatedString  
 -- String concatenation by using ISNULL  
 SELECT 'Value1 '+'Value2 '+isnull(NULL, 'NULL-Replaced') AS ConcatenatedString  
  • Mar 25 / 2009
  • 0
dbDigger, User Defined Functions UDF

Invalid use of side-effecting or time-dependent operator in ‘INSERT’ within a function

The error message
Invalid use of side-effecting or time-dependent operator in ‘INSERT’ within a function

is encountered when an attempt is made to apply a DML statement through a function (user defined function)in your SQL Server database. To avoid the error make sure that your function is not performing any DML operation and also it is not calling any stored procedure to do so. A alternate approach is that if you don’t have to use function within query, you can write it as stored procedure.

  • Mar 25 / 2009
  • 0
dbDigger, User Defined Functions UDF

Invalid use of side-effecting or time-dependent operator in ‘UPDATE’ within a function.

The error message
Invalid use of side-effecting or time-dependent operator in ‘UPDATE’ within a function.

may be found if you try to update any value through SQL Server user defined function (UDF) or even through CLR. To avoid error update should be avoided through any function. A alternate approach is that if you don’t have to use function within query, you can write it as stored procedure.

  • Sep 15 / 2008
  • 1
dbDigger, User Defined Functions UDF

Invalid use of side-effecting or time-dependent operator in ‘PRINT’ within a function.

If you get error message
“Invalid use of side-effecting or time-dependent operator in ‘PRINT’ within a function.”
in your function (UDF), then it means you are using print statement in UDF.
Using print in UDF is not allowed. As alternate try to use select statement compatible with your return data type.
Another reason may to create a non deterministic UDF. In TSQL, UDF function must be deterministic. It must always return the same value when using the same parameter(s). For example if you use NEWID() function in UDF then you will get the mentioned error.

For example, consider the following script to create UDF in which we used RAND(). Executing it will generate the mentioned error.


Also make sure that you are not using transnational processing on the database in a UDF. Also you can´t execute store procedures in a UDF or you may get above error.

Consult us to explore the Databases. Contact us