SQLSourceSafe

Working with User Defined Functions

Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. Microsoft SQL Server 2000 does not limit users to the built-in functions defined as part of the Transact-SQL language, but allows users to create their own user-defined functions.

User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.

Adding a New User Defined Function

To add a new user defined function, you can:

Right click on the User Defined Functions node on the SQL Server pane, then select Add New User Defined Function from the context menu; or
Select Database\Add New User Defined Function from the main menu; or
Click on the Add New User Defined Function button on the toolbar.

Then enter a name in the Enter User Defined Function Name dialog box. Click on the OK button on the dialog box. The Edit User Defined Function dialog appears (see Figure 2).

Figure 1 Enter User Defined Function name dialog

Editing a User Defined Function

To edit a user defined function, you can:

Double click on the User Defined Function; or
Right click on the User Defined Function then select Properties/Edit from the context menu
Highlight the User Defined Function then select Database\Edit from the main menu

Figure 2 Edit User Defined Function Dialog

When the Edit User Defined Function dialog appears, you can modify the script of the User Defined Function in the window. Chick on the Check Syntax button to check the script syntax you have entered. If there is no error in syntax, click on the Save button will save the script to the SQL Server database.

Deleting a User Defined Function

To delete a user defined function, you can:

Right click on the user defined function node that you want to delete then select Delete from the context menu; or
Highlight the user defined function node that you want to delete then select Database\Delete from the main menu.

It will delete the selected user defined function.

Renaming a User Defined Function

This operation allows you to rename a user-defined function.

Right click on the user defined function item that you want to rename in the SQL Server Pane. Then Select Rename from the context menu; or
Highlight the user defined function item you want to rename in the SQL Server Pane, then select Database\Rename from the main menu.