Creating User Defined Functions In SQL Server 2000

Creating User Defined Functions In SQL Server 2000 - What is a user-defined function?
(Page 2 of 6 )

A UDF is a module, which is attached to a database in Microsoft SQL Server 2000. UDF's can accept parameters and can return a value. If you're familiar with stored procedures, then you'll be glad to know that UDF’s can contain code in a similar way to stored procedures. UDF's, however are limited to modifying only local variables (those whose lifetime is within the scope of the UDF), meaning that you can’t use SELECT, INSERT, DELETE, etc statements to modify any tables outside of the scope of the UDF.

What good are UDF's if you can't modify relational data then? Well, they're great for creating functions that need to work with data from databases, but don't actually need to modify the database. You can still retrieve and manipulate tables but can't physically commit changes to them.

UDF's support a wide range of functionality and just because you can’t commit write changes to a database, it doesn't mean that you should turn a blind eye to them. Here's a list of the statements that you can include in the body of a UDF:

• Flow control statements such as IF…ELSE, CASE, GOTO labels, etc.
• UPDATE, INSERT and DELETE commands to modify the contents of tables which are local to the UDF and have been created using SQL Server 200's news TABLE variable type.
• The EXECUTE command to execute stored procedures and extended stored procedures.
• The SET and SELECT statements to assign a value to a variable.
• SELECT statements to retrieve field values from tables into local variables.
• CURSOR, OPEN CURSOR, CLOSE CURSOR, DEALLOCATE CURSOR and FETCH NEXT FROM statements as long as the cursor is both created and disposed of within the scope of the UDF. Note that actual rows can't be returned, and they must be explicitly returned into type-matching variables using the FETCH NEXT INTO statement.
• DECLARE statements to create variables local (in terms of scope) to the UDF.
• Internally defined SQL variables which are prepended with "@@", such as @@ERROR and @@FETCH_STATUS.

As you may've guessed, a UDF is in some ways similar to a stored procedure. Here’s a list of how UDF's are different to stored procedures:

• Scope of modification: UDF's aren't allowed to modify the physical state of a database using INSERT, UPDATE or DELETE statements. They can only work with local data.
• Output parameters: UDF's don't have the ability to return output parameters to the calling function. They do however let us return a single scalar value or a locally created table.
• Error handling: In UDF's, if an error occurs during a call to a stored procedure or a trigger, then that statement is terminated and flow control continue through the UDF.
• Calling conventions: One of the major differences between UDF's and stored procedures is that a UDF can be called through a SQL statement without using the EXECUTE statement.

Now that we've got a better understanding of what a user-defined function actually is, let's create some example UDF's.
blog comments powered by Disqus