|
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.
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: Deleting a Function
|
|
- Server: In the Object Explorer, under the Scalar-Valued Functions node,
right-click Payroll.CalculateDailySalary and click Delete
- pkatts: In the Object Explorer, under the Scalar-Valued Functions node,
right-click Payroll.CalculateWeeklySalary and click Delete
- gmonay: In the Object Explorer, under the Scalar-Valued Functions node,
right-click Payroll.EvaluateYearlySalary and click Delete
- rkouma: In the Object Explorer, under the Scalar-Valued Functions node,
right-click Payroll.EvaluateWeeklySalary and click Delete
- operez: In the Object Explorer, under the Scalar-Valued Functions node,
right-click Payroll.GetMonthlySalary and click Delete
- All Computers: In the Delete Object dialog box, click OK
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
|
Practical Learning: Declaring Local Variables
|
|
- All Computers: Click inside the Query window and press Ctrl + A
- Server: To create a new function, type the following code:
CREATE FUNCTION Payroll.CalculateDailySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @HourlySalary Decimal(8, 2),
@FullName nvarchar(100);
SET @HourlySalary = 24.15;
RETURN @HourlySalary * 8.00
END;
GO
- pkatts: Type:
CREATE FUNCTION Payroll.CalculateWeeklySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @HourlySalary Decimal(8, 2),
@WeeklyHours Real,
@FullName nvarchar(100);
SET @HourlySalary = 24.15;
SET @WeeklyHours = 42.50;
RETURN @HourlySalary * @WeeklyHours
END;
GO
- gmonay: Type:
CREATE FUNCTION Payroll.EvaluateWeeklySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @HourlySalary Decimal(8, 2),
@Monday Real,
@Tuesday Real,
@Wednesday Real,
@Thursday Real,
@Friday Real,
@Saturday Real,
@Sunday Real,
@WeeklyTime Real,
@FullName nvarchar(100);
SET @Monday = 8.00;
SET @Tuesday = 6.50;
SET @Wednesday = 8.50;
SET @Thursday = 9.00;
SET @Friday = 8.50;
SET @Saturday = 0.00;
SET @Sunday = 0.00;
SET @WeeklyTime = @Monday + @Tuesday + @Wednesday +
@Thursday + @Friday + @Saturday +
@Sunday;
SET @HourlySalary = 16.15;
RETURN @HourlySalary * @WeeklyTime;
END;
GO
- rkouma: Type:
CREATE FUNCTION Payroll.GetMonthlySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @HourlySalary Decimal(8, 2),
@Week1Time Real,
@Week2Time Real,
@Week3Time Real,
@Week4Time Real,
@TotalTime Real,
@FullName nvarchar(100);
SET @HourlySalary = 12.65;
SET @Week1Time = 44.50;
SET @Week2Time = 35.00;
SET @Week3Time = 40.00;
SET @Week4Time = 38.00;
SET @TotalTime = @Week1Time + @Week2Time +
@Week3Time + @Week4Time;
RETURN @HourlySalary * @TotalTime;
END;
GO
- operez: Type:
CREATE FUNCTION Payroll.EvaluateYearlySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @HourlySalary Decimal(8, 2);
SET @HourlySalary = 20.25;
RETURN @HourlySalary * 8 * 5 * 4 * 12;
END;
GO
- All Computers: Press F5 to execute the statement
- All Computers: Click inside the Query window and press Ctrl + A
- Server: To call the function, type the following code:
SELECT BethesdaCarRental1.Payroll.CalculateDailySalary()
AS [Daily Salary];
GO
- pkatts: Type:
SELECT BethesdaCarRental1.Payroll.CalculateWeeklySalary()
AS [Weekly Salary];
GO
- gmonay: Type:
SELECT BethesdaCarRental1.Payroll.EvaluateWeeklySalary()
AS [Weekly Salary];
GO
- rkouma: Type:
SELECT BethesdaCarRental1.Payroll.GetMonthlySalary()
AS [Monthly Salary];
GO
- operez: Type:
SELECT BethesdaCarRental1.Payroll.EvaluateYearlySalary()
AS [Weekly Salary];
GO
- All Computers: Execute the code by pressing F5
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.
|
We have already seen that a function's name is also
followed by parentheses. If the function doesn't use an external value,
its parentheses can be left empty. If a function will use an external
value, when you create the function, you must specify a name and the type
of value of the parameters. The name of the parameter is created with the @
sign, like a variable as we saw in the previous lesson. Here is an example:
CREATE FUNCTION Addition(@Number1 Decimal(6,2))
When a function takes a parameter, in the body of the
function, you can use the parameter as if you knew its value, as long as
you respect the type of that value. Here is an example:
CREATE FUNCTION Addition(@Number1 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
RETURN @Number1 + 1450
END
|
 |
|
Calling a Parameterized Function
|
|
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:
|
A Function With Various Arguments |
|
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:
|
Practical
Learning: Creating Functions With Arguments
|
|
- Server: In the Object Explorer, under the Scalar-Valued Functions node,
right-click Payroll.CalculateDailySalary and click Delete
- pkatts: In the Object Explorer, under the Scalar-Valued Functions
node, right-click Payroll.GetMonthlySalary and click Delete
- gmonay: In the Object Explorer, under the Scalar-Valued Functions
node, right-click Payroll.EvaluateYearlySalary and click Delete
- rkouma: In the Object Explorer, under the Scalar-Valued Functions
node, right-click Payroll.EvaluateWeeklySalary and click Delete
- operez: In the Object Explorer, under the Scalar-Valued Functions
node, right-click Payroll.CalculateWeeklySalary and click Delete
- All Computers: In the Delete Object dialog box, click OK
- All Computers: Click inside the Query window and press Ctrl + A
- Server: To add arguments, type:
CREATE FUNCTION Payroll.CalculateDailySalary(@HourlySalary SmallMoney)
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @Weekly SmallMoney
SELECT @Weekly = @HourlySalary * 8.00
RETURN @Weekly
END;
GO
- pkatts: Type:
CREATE FUNCTION Payroll.CalculateWeeklySalary(@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney)
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @Weekly SmallMoney
SELECT @Weekly = @WeeklyHours * @HourlySalary
RETURN @Weekly
END;
GO
- gmonay: Type:
CREATE FUNCTION Payroll.EvaluateWeeklySalary(@HourlySalary Decimal(8, 2),
@Monday Real, @Tuesday Real, @Wednesday Real, @Thursday Real,
@Friday Real, @Saturday Real, @Sunday Real,
@FullName nvarchar(100))
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @Weekly SmallMoney,
@WeeklyTime Real;
SET @WeeklyTime = @Monday + @Tuesday + @Wednesday +
@Thursday + @Friday + @Saturday + @Sunday;
SELECT @Weekly = @WeeklyTime * @HourlySalary
RETURN @Weekly;
END;
GO
- rkouma: Type:
CREATE FUNCTION Payroll.GetMonthlySalary(@HourlySalary Decimal(8, 2),
@Week1Time Real, @Week2Time Real, @Week3Time Real,
@Week4Time Real, @FullName nvarchar(100))
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @Monthlyly SmallMoney, @MonthlyTime SmallMoney;
SELECT @MonthlyTime = @Week1Time + @Week2Time +
@Week3Time + @Week4Time;
SELECT @Monthlyly = @MonthlyTime * @HourlySalary;
RETURN @Monthlyly;
END;
GO
- operez: Type:
CREATE FUNCTION Payroll.EvaluateYearlySalary(@HourlySalary SmallMoney)
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE @Yearly SmallMoney;
SELECT @Yearly = @HourlySalary * 8 * 5 * 4 * 12;
RETURN @Yearly;
END;
GO
- All Computers: Press F5 to create the function
- All Computers: Click inside the top section of the Query window
and press Ctrl + A
- Server: Type the following:
DECLARE @Hourly SmallMoney
SELECT @Hourly = 18.62
SELECT N'Hervey Arndt' As [Employee Name],
@Hourly As [Hourly Salary],
BethesdaCarRental1.Payroll.CalculateDailySalary(@Hourly)
AS [Weekly Salary];
GO
- pkatts: Type the following:
DECLARE @Hours Decimal(5,2),
@Hourly SmallMoney
SELECT @Hours = 42.50
SELECT @Hourly = 18.62
SELECT N'Marthe Engolo' As [Employee Name],
@Hours As [Weekly Time],
@Hourly As [Hourly Salary],
BethesdaCarRental1.Payroll.CalculateWeeklySalary(@Hours, @Hourly)
AS [Weekly Salary];
GO
- gmonay: Type the following:
DECLARE @Hourly SmallMoney, @Monday Real, @Tuesday Real,
@Wednesday Real, @Thursday Real, @Friday Real,
@Saturday Real, @Sunday Real, @FullName nvarchar(100);
DECLARE @WeeklyTime decimal(5, 2);
SET @Monday = 6.00;
SET @Tuesday = 8.00;
SET @Wednesday = 7.00;
SET @Thursday = 6.00;
SET @Friday = 7.50;
SET @Saturday = 0.00;
SET @Sunday = 0.00;
SELECT @Hourly = 12.84
SELECT @WeeklyTime = @Monday + @Tuesday + @Wednesday +
@Thursday + @Friday + @Saturday + @Sunday
SELECT N'James Holms' As [Employee Name],
@WeeklyTime As [Weekly Time],
@Hourly As [Hourly Salary],
BethesdaCarRental1.Payroll.EvaluateWeeklySalary(@Hourly, @Monday, @Tuesday,
@Wednesday, @Thursday, @Friday, @Saturday, @Sunday, @Hourly)
AS [Weekly Salary];
GO
- rkouma: Type the following:
DECLARE @Week1 Decimal(5,2), @Week2 Decimal(5,2),
@Week3 Decimal(5,2), @Week4 Decimal(5,2),
@Hourly SmallMoney, @Name nvarchar(50);
SELECT @Week1 = 42.50;
SELECT @Week2 = 52.50;
SELECT @Week3 = 30.00;
SELECT @Week4 = 46.00;
SELECT @Hourly = 14.65;
SELECT @Name = N'Ernest Papillon';
SELECT @Name As [Employee Name],
@Hourly As [Hourly Salary],
BethesdaCarRental1.Payroll.GetMonthlySalary(@Hourly,
@Week1, @Week2, @Week3, @Week4, @Name)
AS [Monthly Salary];
GO
- operez: Type the following:
DECLARE @Hourly SmallMoney, @DailySalary Smallmoney,
@WeeklySalary Smallmoney, @MonthlySalary Smallmoney;
SELECT @Hourly = 14.45;
SELECT @DailySalary = @Hourly * 8;
SELECT @WeeklySalary = @Hourly * 40;
SELECT @MonthlySalary = @WeeklySalary * 4;
SELECT N'Patrick Bibang' As [Employee Name],
@Hourly As [Hourly Salary],
@DailySalary As [DailyS alary],
@WeeklySalary AS [Weekly Salary],
@MonthlySalary As [Monthly Hours],
BethesdaCarRental1.Payroll.EvaluateYearlySalary(@Hourly)
AS [Yearly Salary];
GO
- All Computers: Press F5 to execute the statement
- All Students: Close Microsoft SQL Server
- All Students: When asked whether you want to save, click No
- Server:
|
When a function with argument is called, a value must be
passed for each argument. Here is an example of such a function:
USE Exercise;
GO
CREATE FUNCTION CalculateTaxAmount(@Price money, @Rate decimal(6, 2))
RETURNS decimal(6, 3)
AS
BEGIN
DECLARE @ResultVar money
SELECT @ResultVar = @Price * @Rate / 100
RETURN @ResultVar
END
GO
Here are examples of calling the function:
USE Exercise;
GO
PRINT dbo.CalculateTaxAmount(140.00, 5.75);
PRINT dbo.CalculateTaxAmount(195.95, 5.75);
PRINT dbo.CalculateTaxAmount(250.00, 7.55);
PRINT dbo.CalculateTaxAmount(125.95, 5.75);
GO
This would produce:
8.05
11.27
18.88
7.24
In some cases, if the function is usually called with the
same value for an argument, you can specify a default value for that argument.
When such a function is called, you (or the user) can omit the value of the
argument.
|
 |
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
|
Functions and Permissions |
|
|
The Right to Create Functions |
|
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:
|
The Right to Execute Functions |
|
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:
|
Practical
Learning: Ending the Lesson |
|
- Server: Launch Microsoft SQL Server and click Connect
- Server: In the Object Explorer, expand Databases
- Server: Under the Databases node, right-click
BethesdaCarRental1 and click Delete
- Server: In the dialog box, click OK
- Server: Close Microsoft SQL Server
|
|
