There are various ways you can start the creation of a function:
CREATE FUNCTION FunctionName()
For a function to be useful, it must produce a result. This is also said that the function returns a result or a value. When creating a function, you must specify the type of value that the function would return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. Here is a simple example:
CREATE FUNCTION Addition() RETURNS Decimal(6,3)
After specifying the type of value that the function would return, you can create a body for the function. The body of a function starts with the BEGIN and ends with the END keywords. Here is an example:
CREATE FUNCTION Addition() RETURNS Decimal(6,3) BEGIN END
Optionally, you can type the AS keyword before the BEGIN keyword:
CREATE FUNCTION Addition() RETURNS Decimal(6,3) AS BEGIN END
Between the BEGIN and END keywords, which is the section that represents the body of the function, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is:
CREATE FUNCTION Addition() RETURNS Decimal(6,3) AS BEGIN RETURN Expression END
Here is an example
CREATE FUNCTION GetFullName() RETURNS nvarchar(100) AS BEGIN RETURN N'Doe, John' END
Because a function returns a value, you can use that value as you see fit. For example, you can use either PRINT or SELECT to display the function's value in a query window. Here is an example that calls the above Addition() function:
As an alternative, to call a function, in the Object Explorer, right-click its name, position the mouse on Script Function As, SELECT To, and click New Query Editor Window.
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.
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.
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.
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 RETURNS 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
In order to carry its assignment, a function can be provided with some values. Put it another way, when you create a function, instead of, or in addition to, local variables, you may want the code that will call the function to provide the values needed to perform the assignment. For example, imagine you want to create a function that would generate employees email addresses when a user has entered a first and last name. At the time you are creating the function, you cannot know or predict the names of employees, including those who have not even been hired yet. In this case, you can write the whole function but provide one or more placeholders for values that would be supplied when the function is called.
An external value that is provided to a function is called a parameter. A function can also take more than one parameter. Therefore, when you create a function, you also decide whether your function would take one or more parameters and what those parameters, if any, would be.
CREATE FUNCTION Addition(@Number1 Decimal(6,2)) RETURNS Decimal(6,2) BEGIN RETURN @Number1 + 1450 END
When you call a function that takes one parameter, you must supply a value for that argument. To do this, type the value of the parameter in the parentheses of the function. Here is an example:
Instead of only one parameter, you can also create a function that takes more than one parameter. In this case, separate the arguments in the parentheses of the function with a comma. Here is an example:
CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2))
Once again, in the body of the function, you can use the parameters as if you already knew their value. You can also declare local variables and involve them with parameters as you see fit. Here is an example:
CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2)) RETURNS Decimal(6,2) BEGIN DECLARE @Result Decimal(6,2) SET @Result = @Number1 + @Number2 RETURN @Result END; GO
When calling a function that takes more than one parameter, in the parentheses of the function, provide a value for each parameter, in the exact order they appear in the parentheses of the function. Here is an example:
PRINT Variables1.dbo.Addition(1450, 228);
You can also pass the names of already declared and initialized variables. Here is an example that calls the above function:
DECLARE @Nbr1 Decimal(6,2), @Nbr2 Decimal(6,2) SET @Nbr1 = 4268.55 SET @Nbr2 =26.83 SELECT @Nbr1 As First, @Nbr2 As Second, Variables1.dbo.Addition(@Nbr1, @Nbr2) AS Result
This would produce:
To specify a default value for an argument, in the parentheses of the function, after the name and data type of the argument, type =, followed by the desired value. Here is an example:
USE Exercise; GO CREATE FUNCTION CalculateTaxAmount(@Price money, @Rate decimal(6, 2) = 5.75) RETURNS decimal(6, 2) AS BEGIN DECLARE @ResultVar money SELECT @ResultVar = @Price * @Rate / 100 RETURN @ResultVar END GO
When calling a function that has a default value for an argument, you don't have to pass a value for that argument, although you can if you want. When calling the function, in the placeholder of the argument, type the DEFAULT keyword. Here are examples of calling the latest version of our function:
USE Exercise; GO PRINT dbo.CalculateTaxAmount(140.00, DEFAULT); PRINT dbo.CalculateTaxAmount(195.95, 5.75); PRINT dbo.CalculateTaxAmount(250.00, 7.55); PRINT dbo.CalculateTaxAmount(125.95, default); GO
If you are working in a large environment with many users, you may not want all of them to add new functions to a database. In fact, you can control who has the ability to create functions and who can execute them.
The primary permission of a function is named CREATE FUNCTION. To visually allow a user to create functions, access the Database Properties of the database. In the left frame, click Permissions. In the Users or Roles list, click the user. In the Permissions section, locate the Create Function row:
To programmatically grant the CREATE FUNCTION permission, type it after the GRANT keyword and specify the user after TO. Here is an example:
CREATE USER [Raymond Kouma] FOR LOGIN rkouma; GO USE Exercise1; GO GRANT CREATE FUNCTION TO rkouma; GO
On the other hand, if you want to prevent a user from creating functions, use the DENY operator. Here is an example:
USE Exercise1; GO DENY CREATE FUNCTION TO [Raymond Kouma]; GO
This would not allow a user to create a new function in the designated database:
If you work in a production environment, you may want to have only some developers creating functions while the other people can only execute or test the existing functions. To exercise that control, the database is equipped with a permission named EXECUTE that is associated with a function. To visually give this right, in the Database Properties, locate the Exercute entry in the Permissions section.
To programmatically grant the right to execute a function, use the following formula:
GRANT EXECUTE ON OBJECT::[Schema.]FunctionName TO User
Start with the GRANT EXECUTE ON OBJECT:: expression. If you are using a Transact-SQL built-in function (next lesson), you can omit the schema. This is followed by the name of the function, TO, and the name of the user. Here is an example:
USE Exercise1; GO GRANT EXECUTE ON OBJECT::dbo.Addition TO [Raymond Kouma]; GO
On the other hand, to prevent a user from executing a function, deny the EXECUTE permission. Here is an example:
USE Exercise1; GO DENY EXECUTE ON OBJECT::dbo.Addition TO [Raymond Kouma]; GO
This would produce: