Introduction to Transact SQL User-Defined Functions

By Karen Gayda

Microsoft added a host of new features to its SQL Server 2000 product, and one of the most interesting for SQL programmers is the user-defined function. Adding functions to the Transact SQL language has solved many code reuse issues and provided greater flexibility when programming SQL queries.

This article focuses on the syntax, structure, and application of Transact SQL user-defined functions. The material presented is based on the SQL Server 2000 Beta Release 2.

Types of Functions

SQL Server 2000 supports three types of functions: scalar, in-line table functions, and multistatement table functions. All three types of functions accept parameters of any scalar data type except rowversion. Scalar functions return a single scalar value and in-line and multistatement table functions return a table data type. (NOTE: the table data type is new in SQL Server 2000.)

I. Scalar Functions

Scalar functions return a data type such as int, money, varchar, real, etc. They can be used anywhere a built-in SQL function is allowed. The syntax for a scalar function is the following:

CREATE FUNCTION [owner_name.] function_name
	( [{ @parameter_name  scalar_parameter_type [ = default]} [,..n]])
RETURNS scalar_return_type
[WITH <function_option> >::={SCHEMABINDING | ENCRYPTION]
	RETURN scalar_expression

A simple scalar function to cube a number would look like this:

CREATE FUNCTION dbo.Cube( @fNumber float)
	RETURNS float
	RETURN(@fNumber * @fNumber * @fNumber)

Surprisingly, user-defined functions (UDFs) support recursion.  Here is an
SQL Server 2000 UDF using the standard factorial example:

CREATE FUNCTION dbo.Factorial ( @iNumber int )
DECLARE @i	int

	IF @iNumber <= 1
		SET @i = 1
		SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )

II. In-Line Table Functions

In-line table functions are functions that return the output of a single SELECT statement as a table data type. Since this type of function returns a table, the output can be used in joins of queries as if it was a standard table. The syntax for an in-line table function is as follows:

CREATE FUNCTION [owner_name.] function_name
	( [{ @parameter_name  scalar_parameter_type [ = default]} [,..n]])
[WITH <function_option>::={SCHEMABINDING | ENCRYPTION}]
RETURN [(] select_statement [)]

An in-line function to return the authors from a particular state would
look like this:

CREATE FUNCTION dbo.AuthorsForState(@cState char(2) )
RETURN (SELECT * FROM Authors WHERE state = @cState)

III. Multistatement Table Functions Multistatement table functions are similar to stored procedures except that they return a table. This type of function is suited to address situations where more logic is required than can be expressed in a single query. The following is the syntax for a multistatement table function:

CREATE FUNCTION [owner_name.] function_name
	( [{ @parameter_name  scalar_parameter_type [ = default]} [,..n]])
[WITH <function_option> >::={SCHEMABINDING | ENCRYPTION]

Hierarchical data, such as an organizational structure, is an example of data that cannot be gathered in a single query. The Northwind Company database's Employees table contains a field called ReportsTo that contains the EmployeeID of the employee's manager. GetManagerReports is a multistatement table function that returns a list of the employees who report to a specific employee, either directly or indirectly.

CREATE FUNCTION dbo.GetManagerReports ( @iEmployeeID int )
RETURNS @ManagerReports TABLE
   	EmployeeID			int,
   	EmployeeFirstName     		nvarchar(10),
   	EmployeeLastName 		nvarchar(20),
	Title				nvarchar(30),
	TitleOfCourtesy			nvarchar(25),
	Extension			nvarchar(4),
   	ManagerID			int


@iRowsAdded	int, 		-- Counts rows added to
-- table with each iteration
	@PREPROCESSED			tinyint,		-- Constant
for record prior
-- to processing
	@PROCESSING			tinyint,		-- Constant
for record
-- being processed
	@POSTPROCESSED			tinyint		-- Constant for
records that
-- have been processed


	DECLARE	@tblReports TABLE (
-- Holds employees added with each pass thru source employees table
		EmployeeID				int,
   		EmployeeFirstName     			nvarchar(10),
   		EmployeeLastName 			nvarchar(20),
		Title					nvarchar(30),
		TitleOfCourtesy				nvarchar(25),
		Extension				nvarchar(4),
   		ManagerID				int,
		ProcessedState				tinyint

	--Begin by adding employees who report to the Manager directly.
	INSERT INTO @tblReports
	SELECT EmployeeID, FirstName, LastName, Title, TitleOfCourtesy,
Extension, ReportsTo, @PREPROCESSED
		FROM Employees
	WHERE ReportsTo = @iEmployeeID

	--Save number of direct reports
	SET @iRowsAdded = @@ROWCOUNT

	-- Loop through Employees table until no more iterations are necessary
	--	(e.g., no more rows added) to add all indirect reports.
	WHILE @iRowsAdded > 0
		--Set just added employees ProcessedState to PROCESSING
-- (for first pass)
		UPDATE @tblReports
			SET ProcessedState = @PROCESSING
		WHERE ProcessedState = @PREPROCESSED

		--Add employees who report to Managers in
-- ProcessedState = PROCESSING
		INSERT INTO @tblReports
SELECT e.EmployeeID, e.FirstName, e.LastName, e.Title,
e.TitleOfCourtesy, e.Extension, e.ReportsTo, @PREPROCESSED
			FROM Employees e
INNER JOIN @tblReports r ON e.ReportsTo = r.EmployeeID
		WHERE r.ProcessedState = @PROCESSING
			AND e.ReportsTo <> @iEmployeeID

		--Save number of rows added for this iteration
		SET @iRowsAdded = @@ROWCOUNT

--Set ProcessedState to POSTPROCESSED for Managers whose
--reports were added in this iteration
		UPDATE @tblReports
			SET ProcessedState = @POSTPROCESSED
		WHERE ProcessedState = @PROCESSING

	--Save all data to output table
	INSERT INTO @ManagerReports
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Title,
TitleOfCourtesy, Extension, ManagerID
		FROM @tblReports

The output of this function would be used in the same manner as a standard table. Figure 1 demonstrates JOINING the output of GetManagerReports with the Employees table to produce a listing of the organizational structure of the Northwind Company:

User function used in JOIN query
Figure 1: User function used in JOIN query.

Invoking Functions

There are a few syntax idiosyncrasies to observe when invoking user-defined functions. SQL Server 2000 provides some system-level user-defined functions in the Master database. These system functions are invoked with a slightly different syntax than ones that you would create. System functions that return a table have the following syntax:

::function_name ([argument_expr], [,...])

System functions that return a scalar value use this syntax:

function_name ([argument_expr], [,...])

User-created scalar and rowset functions are invoked in exactly the same
manner.  The syntax for invoking a user-created function looks like this:

[database_name] owner_name. function_name ([argument_expr], [,...])


User-defined functions do have some restrictions placed upon them. Not every SQL statement or operation is valid within a function. The following lists enumerate the valid and invalid function operations: Valid:

  • Assignment statements
  • Control-flow statements
  • Variable declarations
  • SELECT statements that modify local variables
  • Cursor operations that fetch into local variables
  • INSERT, UPDATE, DELETE statement that act upon local table variables


  • Built-in, nondeterministic functions such as GetDate()
  • Statements that update, insert, or delete tables or views
  • Cursor fetch operations that return data to the client

Performance Implications

Using UDFs will impact the performance of queries. The extent of the performance impact depends upon how and where you use a user-defined function. This is also true of built-in functions. However, UDFs have the potential for more dramatic performance hits than built-in functions. You should exercise caution when implementing functions in your queries and perform benchmarking tests to insure that the benefits of using your functions exceed the performance costs of using them.

Uses for Functions

Check constraints

Scalar user-defined functions can be used as check constraints for columns in table definitions. As long as an argument to the function is a constant or built-in function or an argument is the column being checked, the function may be used to validate the column's value. These UDF check constraints provide the ability to use more complex logic for determining acceptable column values than Boolean expressions or LIKE patterns would allow.

The following function validates that a serial number follows a specific pattern and portions of the serial number match a specific algorithm for a product type.

CREATE FUNCTION dbo.ValidSerialNumber( @nvcSerialNumber nvarchar(50))

@bValid		BIT,
	@iNumber		INT

	--default to invalid serial number
	SET @bValid = 0

	--Home Office Product
	IF @nvcSerialNumber LIKE
		SET @iNumber = CONVERT(int,RIGHT(@nvcSerialNumber,4))
		IF @iNumber % 7 = 2
			SET @bValid = 1

	-- Video Game
	IF @nvcSerialNumber LIKE '[0-9][0-9][0-9][A-Z][0-9]5[A-Z]'

		SET @iNumber = CONVERT(int,LEFT(@nvcSerialNumber, 3))
		IF @iNumber % 2 = 0
			SET @bValid = 1

RETURN ( @bValid)

CREATE TABLE dbo.CustomerProduct
	ProductID		int		NOT NULL,
	SerialNumber		nvarchar(20)	NOT NULL
	CHECK(dbo.ValidSerialNumber(SerialNumber) = 1)

Computed columns

Scalar functions can be used to compute column values in table definitions. Arguments to computed column functions must be table columns, constants, or built-in functions. This example shows a table that uses a Volume function to compute the volume of a container:

CREATE FUNCTION dbo.Volume ( 		@dHeight 	decimal(5,2),
@dLength 	decimal(5,2),
@dWidth	decimal(5,2) )
RETURNS decimal (15,4)
	RETURN (@dHeight * @dLength * @dWidth )

CREATE TABLE dbo.Container
	ContainerID		int		NOT NULL
	MaterialID		int		NOT NULL
REFERENCES Material(MaterialID),
	ManufacturerID		int		NOT NULL
	Height			decimal(5,2)	NOT NULL,
	Length			decimal(5,2)	NOT NULL,
	Width			decimal(5,2) 	NOT NULL,
	Volume AS
			dbo.Volume( Height, Length, Width )

You should note that computed columns might be excluded from being indexed if user-defined functions determine their value. An index can be created on the computed column if the user-defined function is deterministic (e.g., always returns the same value given the same input).

Default constraints

Default column values can be set with user-defined functions. UDFs can be very useful when a hard-coded value or built-in function does not suffice. For example, if a doctor's office wished to save a patient's appointment preference, a user-defined function could calculate the default day and time in a function by using the current date/time when the patient's record was created. If the patient's record were created on a Friday at 10:34 AM the AppointmentPref column would default to "Friday at 10:00" using the following function:

CREATE FUNCTION dbo.AppointmentPreference ( @dtDefaultDateTime datetime )
RETURNS nvarchar(50)

DECLARE @nDay 		nvarchar(10),
	@nHour		nvarchar(6),
	@nPreference		nvarchar(50),
	@tiHour		tinyint

	--Get date description
	SET @nDay = DATENAME(dw, @dtDefaultDateTime )

	--Find current hour
	SET @tiHour = DATEPART(hh,@dtDefaultDateTime)

	--Use only 12-hour times
	IF @tiHour > 12
		SET @tiHour = @tiHour - 12

	--Don't allow appointments during lunch
	IF @tiHour = 12
		SET @tiHour = 1

	-- These are invalid hours
	IF @tiHour IN(5,6,7,8)
		SET @tiHour = 4

	--Create preference text
	SET @nPreference = RTRIM(@nDay) + '''s at ' +
CONVERT(varchar(2),@tiHour) + ':00'
	RETURN ( @nPreference)

CREATE TABLE dbo.Patient
	FirstName		nvarchar(20)	NOT NULL,
	LastName		nvarchar(20)	NOT NULL,
	Addr1			nvarchar(50),
	Addr2			nvarchar(50),
	City			nvarchar(50),
	State			nvarchar(2),
	ZipCode		nvarchar(20),
	HomePhone		nvarchar(20),
	WorkPhone		nvarchar(20),
AppointmentPref	nvarchar(50)
DEFAULT (dbo.AppointmentPreference(GETDATE()))



Scalar user-defined functions can be used to assign values to scalar variables. They may be used in any situation where a scalar built-in function may be used.

DECLARE @fCube 	float

SET @fCube = dbo.Cube( 4.5 )

Control flow

Scalar user-defined functions may be used to control program flow when used in Boolean expressions.

IF dbo.ValidSerialNumber('002A15A') = 1
	PRINT 'Yes'


Case expressions

User-defined functions that return a scalar value can be used in any of the cases of CASE expressions. The following example uses the DailySpecial function in a case function to determine what to display for a given day:

CREATE FUNCTION dbo.DailySpecial( @nvcDay nvarchar(10))

DECLARE @nvcSpecial	nvarchar(100)

	SET @nvcDay = UPPER(@nvcDay)

	IF @nvcDay = 'SUNDAY'
		SET @nvcSpecial = 'Roast beef with green beans and
baked potato'

	IF @nvcDay = 'MONDAY'
		SET @nvcSpecial = 'Chopped beef with green bean

	IF @nvcDay = 'TUESDAY'
		SET @nvcSpecial = 'Beef stew'

	IF @nvcDay = 'WEDNESDAY'
		SET @nvcSpecial = 'Beef pot pie'

	IF @nvcDay = 'THURSDAY' OR @nvcDay = 'FRIDAY'
		OR @nvcDay = 'SATURDAY'
		SET @nvcSpecial = 'Beef surprise'

RETURN ( @nvcSpecial )

--Use output of DailySpecial function
SELECT   Special =
    CASE DateName(dw, getdate())
WHEN 'Sunday' THEN dbo.DailySpecial('Sunday')
        	WHEN 'Monday' THEN
         	WHEN 'Tuesday' THEN
	WHEN 'Wednesday' THEN dbo.DailySpecial('Wednesday')
         ELSE 'It's a mystery!'


Alternative to views

Rowset functions, functions that return tables, can be used as alternatives to read-only views. Since views are limited to a single select statement, user-defined functions can provide greater functionality than a view. Powerful logic can be used when determining the records returned, which is not possible within a view. Also, views cannot accept parameters so a separate view must be created if the WHERE clause must change for different search c riteria.

Alternative to temporary tables Rowset functions can be used as alternatives to temporary tables. For example, if you wished to find authors in the Pubs database who sold no books in a particular state, you could create a couple of functions that would generate the desired resultset.

To find the quantity of books sold for a particular author in a given state you could write the following function:

CREATE FUNCTION dbo.AuthorPoularityForState ( @cState Char(2))

        SELECT a.au_id, a.au_fname, a.au_lname,
               SUM(s.qty) AS QTY
        FROM Authors a
		INNER JOIN TitleAuthor ta ON a.au_id = ta.au_id
		INNER JOIN Titles t ON t.title_id = ta.title_id
		INNER JOIN Sales s ON s.title_id = t.title_id
		INNER JOIN Stores st ON st.Stor_ID = s.stor_id
	WHERE  st.state = @cState
	GROUP BY a.au_id, a.au_fname, a.au_lname
	ORDER BY QTY DESC, a.au_lname, a.au_fname

You could then create another function that would use the output from the first function to find the authors in a particular state that have not had any sales:

CREATE FUNCTION dbo.ReallyBoringAuthorsForState ( @cState Char(2) )

SELECT a.au_id as AuthorID, a.au_fname AS AuthorFirstName,
	a.au_lname AS AuthorLastName, @cState AS State
	FROM AuthorPopularityForState(@cState ) pa
	RIGHT JOIN authors a ON pa.AuthorID = a.au_id
	WHERE IsNull(pa.UnitsSold, 0) = 0


The following SQL statement would list the California authors who had not sold any books:

SELECT AuthorLastName, AuthorFirstName, AuthorID
	FROM ReallyBoringAuthorsForState('CA')
ORDER BY AuthorLastName, AuthorFirstName

List of authors without book sales
Figure 2: List of authors without book sales

Before the release of SQL Server 2000, temporary tables would likely have been used to generate the interim data to be used for the final query output. By using functions instead of temporary tables, potential table name concurrency problems are avoided. Functions also offer greater code reuse than temporary tables.


As demonstrated , user-defined functions provide many more programming options than there were before UDFs were included in the Transact SQL language. SQL Server programmers have waited a long time for the user-defined functions Microsoft made possible with SQL Server 2000. The advantages of code reusability and of fixing problems in a single routine can now be realized by incorporating UDFs into our designs. Now if we could only be given arrays in the next version.

About the Author

Karen Gayda is an independent software consultant from San Diego, California. She specializes in Web application development using VB/COM, SQL Server, DHTML, and scripting languages. She can be contacted at


  • Le redresseur est parfait pour votre sac à main ou à garder dans le kit lorsque vous êtes sur la route!

    Posted by wxzqyq604 on 07/16/2013 07:58am

    Maintenant, avec tension universelle pour une performance optimale partout dans le monde que vous êtes deg.Hvilemodus est une configuration de sécurité intégré qui vous donne la tranquillité d'esprit, car il s'éteint automatiquement technologie numérique av.Unik signifie que la température est contrôlée automatiquement encore plus rapide pour un meilleur style. New rounder baril de sorte qu'il est facile de créer des boucles parfaites, vagues ou chiquenaudes, ainsi que les supprimer. [url=]ghd pas cher lisseur[/url] GHD droit tige conteneur livfullheten et lambeaux de cheveux Farget. Près de la rivière pour s'assurer que la chaleur est répartie uniformément, ne devient jamais Hoy, reduserer La fargebevaringsteknologi unique avec IONTEC décoloration av Fargen en stylisme de 70% * et rend le Fargen obtient appareils de coiffage beaucoup plus glansfull.GHD ne fait pas ditt de cheveux terne et sec. Moi, sur IMot: ils empêchent fuktighetstap de style par y infliger ditt de cheveux satengioner unique! Ces ladde négatif ionène célébrations segment à la chevelure ladde positif ditt et reduserer sec, krusete cheveux et tiltrekker fuktighetspartikler de Lufta et leur envoyer votre le Trenger de cheveux le plus. [url=]ghd pas cher lisseur[/url] Admirateurs de fer GHD sont en nombre croissant et devraient continuer à increase.As ces caractéristiques uniques rettetanghar GHD qui n'abîment pas les cheveux, vous pouvez les utiliser régulièrement pour coiffer vos cheveux. Divisez vos cheveux en plusieurs sections afin de rendre le processus plus rapidement. Maintenez le lisseur légèrement et faites glisser depuis le sommet jusqu'à la pointe des cheveux, faire pression sur les cheveux raides n'est tout simplement pas nécessaire lorsque vous utilisez tige de style. Application Lisseur GHD légèrement suffit à maintenir belles et élégantes jours de cheveux de hair.Good sont difficiles à trouver avec la pollution croissante et la poussière. Cependant, ghd créé à cet effet, de sorte que vous pouvez être sûr que vos problèmes sont guéris.

  • Trist omgitt Ã¥ kunne endre strøm koblet til kunder

    Posted by mantouhmmm on 06/04/2013 02:15am

    [url=]beats by dre norge[/url] Samtidig er toppen er det ingen fleksibilitet turban, de waggle i tankene er det lett. Som Zorro headset er lett på 145 gram, kan dette bare være et problem hvis du planlegger å hard trening. Det er også verdt å merke seg at solo HD lederskap pakke å kjempe flere headset produsenter, cottoned-på 41 høy friksjon økt bruk av stability.Beats By Dre Studio justbeats Zorro-øretelefonene spiller en feil tempo solo HD. Men vi kan ikke være for harde på disse svakhetene. Loose uegnet for noen god tetning og nytten av de skjulte stemmen porter for å nekte noen forseglet. [url=]beats by dre norge[/url] Det kan også hjelpe med meditasjon, akkurat som theta bølger. Høye nivåer av gamma kalles ioniserende stråling. Det har vært forbundet med å forårsake kreft hos mennesker, men kan også brukes til å behandle kreft, selv om det er årsaken til at personene har det i første place.Theta rytme er assosiert med dyp avslapning, meditasjon og ikke-raske øyebevegelser søvn. Binaural beats som manipulerer Hz av sinnet kan gi disse ønskede effekter. Bruk av binaural lyder har vist seg å indusere søvn og avslapning når du målretter theta hjernebølger. Sammen med alfa-rytmer, kan theta bølger hjelpe en person komme over tilvenningen som sigaretter og alkoholisme. [url=]Beats norge[/url] Beats nær Dre Tour ControlTalk inear Hodetelefoner Purple98.00 billige beats Purple er screechy kvalitet, og ingen verdt shipping.Jul 25, rød-farget hodetelefoner grunn riktig er for sleazy kostnaden allot grade bare én kvalitet. Hugeness Dr Dre Solo HD rød-farget hodetelefonene er monster beats nær dre solo hd hodetelefoner sted salg, slår alle monster solo hd sted øret hodetelefoner markedet hugeness slår av Dr Dre solo hd hugeness slår nær dre solo hd stor Definition Sound, behagelig passform , Light allot Compact handel hver enkelt Lifestyle, ControlTalk.ColorWare gir tilpasse tjenestene sted Beats Solo HD. monster beats tour nær Dre. Beats Solo HD-hodetelefoner er lilje innenfor av dalen ny kompakt utgave av Beats nærheten av Dr. Dre.

  • Your Feet Will appreciate Jordans Footwear and relish the Come upon

    Posted by NopFrufFElurl on 02/28/2013 05:37pm

    Cheap jordans footwear throughout winter season,we've! In case you are pursued for that critical month roughly,inexpensive nike air jordan in the event you such as golf ball or you just about all got information in the basketball World Festival. Your more the with that function experienced distributed which it experienced the other start, in order that we all can take into account the actual fresh storage. This can be no declare individuals side on all fighting shoes in which have been set totally free on this package were all quite nice. Right now we take the act inside the Brand-new AJ The year of 2010 assembly within this Rise Usa We all Version. The idea have to always be mentioned in which Jordani? The year of 2010 offers a single in the best many years in the fresh previous itemising regarding fresh variations of the Nike jordan 4 sequence. Many involving the actual guys experienced at the moment cool The nike jordan footwear that you are advertise to date this kind of year and also involves a new plans in our very republican retro classics that people tends to make dirty on their arms.Don forget arrive close off 6 or seven many years, has been following you needed to carry out with the indicator of the term Melo anyplace close to the particular meaning within a abuse along with Jordani? Athletic shoes in the racket regarding irrepressible approach immediately residents got? Nicely, merchandise are usually relatively a lot more because extremely rousing, however right now there you have a good couple of skateboard footwear Melo to draw seal off, the good news is this individual ended up being inside the individuality for the Jordani? Hearth prolonged, the style regarding composed came about a little. Obviously, the particular product that will majority of AJ Footwear is available in yellow isn't able sky-blue and much more material adored coloring ways. The actual Nike AJ Fire originated the environment Nike jordan 2010 to price your legacy of music involving Nike jordan within hockey, exactly where the particular temporary on the sociable of the wearing actions to the aceded Team Nike jordan sports athletes. The Air The nike jordan This year Fashion gleaming Jordan Get Trip commemoration honors Overall performance Plastics along with height Black/Yellow leather-based for you to consist of for the close off no-sew technical understanding for any smooth encourage story. [url=]air jordan fusions[/url]|

  • Function

    Posted by Roopa on 09/23/2012 10:46pm

    Need to explain in depth.Give some example on UDF and Subquery and their differnces

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Moving from an on-premises environment to Office 365 does not remove the need to plan for disruptions or reduce the business risk requirements for protecting email services. If anything, some risks increase with a move to the cloud. Read how to ease the transition every business faces if considering or already migrating to cloud email. This white paper discusses: Setting expectations when migrating to Office 365 Understanding the implications of relying solely on Exchange Online security Necessary archiving …

  • Enterprises are increasingly looking to platform as a service (PaaS) to lower their costs and speed their time to market for new applications. Developing, deploying, and managing applications in the cloud eliminates the time and expense of managing a physical infrastructure to support them. PaaS offerings must deliver additional long-term benefits, such as a lower total cost of ownership (TCO), rapid scalability, and ease of integration, all while providing robust security and availability. This report …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date