Home

Function Maintenance
   

Introduction
 

Because a function in Transact-SQL is treated as an object, it may need maintenance. Some of the actions you would take include renaming, modifying, or deleting a function.

Renaming a Function

If you create a function and execute it, it is stored in the Scalar-Valued Functions node with the name you gave it. If you want, you can change that name but keep the functionality of the function.

To rename a function, in the Object Explorer, right-click it and click Rename. Type the desired new name and press Enter.
 
       

Deleting a Function

If you create a function and decide that you don't need it any more, you can delete it.

To delete a function in the Object Explorer, locate the function in the Functions section, right-click it and click Delete. The Delete Object dialog box would come up. If you still want to delete the function, click OK; otherwise, click Cancel.

To programmatically delete a function:

  • In a query window, type DROP FUNCTION followed by the name of the function and execute the statement
  • In the Object Explorer, right-click the name of the function, position the mouse on Script Function As, DROP To, and click New Query Editor Window
  • Open a new query window associated with the database that contains the function. Display the Templates Explorer and expand the Function node. Drag the Drop Function node and drop it in the empty query window

Practical Learning Practical Learning: Deleting a Function
  1. On the form, double-click the Database button and change its code as follows:
     
    private void btnDatabase_Click(object sender, EventArgs e)
{
    string strConnection =
		"Data Source=(local);" +
		"Database='UtilityCompany1';" +
		"Integrated Security=yes;";
    string CreateFunction = "DROP FUNCTION EvaluateInvoice;";

    using (SqlConnection connection = new SqlConnection(strConnection))
    {
	SqlCommand command =
		new SqlCommand(CreateFunction, connection);

    	connection.Open();
	command.ExecuteNonQuery();

  	MessageBox.Show(
	    "A function named \"EvaluateInvoice\" has been deleted.");
    }
}
  2. Execute the application and click the Database button
     
    Deleting a Function
  3. Close the form and return to your programming environment

Modifying a Function

As mentioned already, in the body of the function, you define what the function is supposed to take care of. As a minimum, a function can return a simple number, typed on the right side of the RETURN keyword. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
    RETURN 1
END

You can also declare new variables in the body of the function to help in carrying the assignment. A variable declared in the body of a function is referred to as a local variable. Once such a variable has been declared, it can be used like any other variable. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
    DECLARE @Number1 int
    SET @Number1 = 588
    RETURN @Number1 + 1450
END
 
 

Published on Monday 27 December 2007

 
Previous Copyright © 2007 FunctionX, Inc. Next