Home

Introduction to Functions

 

Functions Fundamentals

 

Introduction

A function is a section of code that is used to perform an isolated assignment. Once it has performed its assignment, the function can be accessed to present its result(s).

In Transact-SQL, a function is considered an object. After creating the function object, it becomes part of a database. You can then execute it when necessary.

 

Practical LearningPractical Learning: Introducing Functions

  1. All Computers: Start the computer and log in using your domain account
  2. Server:
    1. Launch Microsoft SQL Server. In the Server Name combo box, make sure the name of the computer is selected. In the Authentication combo box, make sure Windows Authentication is selected. Make sure the account you are using is selected in the User Name combo box. Click Connect
    2. In the Object Explorer, expand Databases
    3. To create a new database, right-click Databases and click New Database...
    4. In the Database Name text box, type BethesdaCarRental1
    5. In the Object Explorer, Right-click Databases and click Refresh
    6. Expand the Databases node followed by the BethesdaCarRental1 node
    7. Under BethesdaCarRental1, right-click Security, position the mouse on New, and click Schema...
    8. In the Schema Name, type Payroll
    9. Click OK
  3. Each Student:
    1. Start Microsoft SQL Server
    2. In the Server Name combo box, select the name of the server or type it
    3. In the Authentication combo box, select SQL Server Authentication
    4. In the User Name combo box, type the name of the domain, followed by \, and followed by the login name you were given
    5. In the Password combo box, type your password
    6. Click Connect
  4. All Computers: On the Standard toolbar, click the New Query button New Query
  5. All computers: Type:
    USE BethesdaCarRental1;
    GO
  6. All computers: To execute, press F5
  7. All computers: Click inside the top section of the Query window and press Ctrl + A to select everything
  8. All Computers: Press Delete

Creating a Function

There are various ways you can start the creation of a function:

  • In the Object Explorer, expand the desired database. Expand the Programmatically node. Expand the Functions node. Right-click Scalar-Valued Function and click New Scalar-Valued Function...

    Creating a Function

    Sample code would be generated for you:
    -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Scalar Function (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- This block of comments will not be included in
    -- the definition of the function.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date, ,>
    -- Description:	<Description, ,>
    -- =============================================
    CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
    (
    	-- Add the parameters for the function here
    	<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
    )
    RETURNS <Function_Data_Type, ,int>
    AS
    BEGIN
    	-- Declare the return variable here
    	DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
    
    	-- Add the T-SQL statements to compute the return value here
    	SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
    
    	-- Return the result of the function
    	RETURN <@ResultVar, sysname, @Result>
    
    END
    GO
    You can then modify to customize it
  • Open an empty Query window. Display the Templates Explorer window and expand the Function node. Drag Create Scalar Function (New Menu) and drop it in the query window
    -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Scalar Function (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- This block of comments will not be included in
    -- the definition of the function.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date, ,>
    -- Description:	<Description, ,>
    -- =============================================
    CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
    (
    	-- Add the parameters for the function here
    	<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
    )
    RETURNS <Function_Data_Type, ,int>
    AS
    BEGIN
    	-- Declare the return variable here
    	DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
    
    	-- Add the T-SQL statements to compute the return value here
    	SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
    
    	-- Return the result of the function
    	RETURN <@ResultVar, sysname, @Result>
    
    END
    GO
  • You can open a new empty query window and start typing your code in it

In Transact-SQL, the primary formula of creating a function is:

CREATE FUNCTION FunctionName()
The Name of a Function

The Name of a Function

We mentioned already that, in Transact-SQL, a function is created as an object. As such, it must have a name. In our lessons, here are the rules we will use to name our functions:

  • A name will start with either an underscore or a letter. Examples are _n, act, or Second
  • After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are _n24, act_52_t
  • A name will not include special characters such as !, @, #, $, %, ^, &, or *
  • We will avoid using spaces in a name, with few exceptions 
  • If the name is a combination of words, each word will start in uppercase. Examples are DateHired, _RealSport, or DriversLicenseNumber

Returning a Value From a Function

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

Practical LearningPractical Learning: Creating Functions

  1. Server: To create a function, type the following statement:
    CREATE FUNCTION Payroll.CalculateDailySalary()
    RETURNS Decimal(8, 2)
    AS
    BEGIN
        RETURN 160.44
    END;
    GO
  2. pkatts: Type:
    CREATE FUNCTION Payroll.CalculateWeeklySalary()
    RETURNS Decimal(8, 2)
    AS
    BEGIN
        RETURN 880.44
    END;
    GO
  3. gmonay: Type:
    CREATE FUNCTION Payroll.EvaluateWeeklySalary()
    RETURNS Decimal(8, 2)
    AS
    BEGIN
        RETURN 880.44
    END;
    GO
  4. rkouma: Type:
    CREATE FUNCTION Payroll.GetMonthlySalary()
    RETURNS Decimal(8, 2)
    AS
    BEGIN
        RETURN 3521.76
    END;
    GO
  5. operez: Type:
    CREATE FUNCTION Payroll.EvaluateYearlySalary()
    RETURNS Decimal(8, 2)
    AS
    BEGIN
        RETURN 42262.00
    END;
    GO
  6. All Computers: To execute the statement, on the SQL Editor toolbar, click the Execute button Execute
  7. All Computers: In the Object Explorer, expand the BethesdaCarRental1 node, expand Programmability. Expand Functions. And expand Scalar-Valued Functions. Notice the presence of the CalculateWeeklySalary node

New Function

Calling a Function

After a function has been created, you can use the value it returns. Using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is:

DatabaseName.dbo.FunctionName()

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:

PRINT Exercise.dbo.GetFullName();

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.

Calling a Function

Practical LearningPractical Learning: Calling a Function

  1. All Computers: Click inside the Query window and press Ctrl + A to select everything
  2. Server: To execute the function we just created, type the following:
    PRINT BethesdaCarRental1.Payroll.CalculateDailySalary();
    GO
  3. pkatts: Type:
    PRINT BethesdaCarRental1.Payroll.CalculateWeeklySalary();
    GO
  4. gmonay: Type:
    PRINT BethesdaCarRental1.Payroll.EvaluateWeeklySalary();
    GO
  5. rkouma: Type:
    PRINT BethesdaCarRental1.Payroll.GetMonthlySalary();
    GO
  6. operez: Type:
    PRINT BethesdaCarRental1.Payroll.EvaluateYearlySalary();
    GO
  7. All Computers: To execute, press F5
  8. Server: To specify a column name for the returned value of a function, change the function call as follows:
    SELECT BethesdaCarRental1.Payroll.CalculateDailySalary() AS [Daily Salary];
    GO
  9. pkatts: Change the function call as follows:
    SELECT BethesdaCarRental1.Payroll.CalculateWeeklySalary() AS [Weekly Salary];
    GO
  10. gmonay: Change the function call as follows:
    SELECT BethesdaCarRental1.Payroll.EvaluateWeeklySalary() AS [Weekly Salary];
    GO
  11. rkouma: Change the function call as follows:
    SELECT BethesdaCarRental1.Payroll.GetMonthlySalary() AS [Monthly Salary];
    GO
  12. operez: Change the function call as follows:
    SELECT BethesdaCarRental1.Payroll.EvaluateYearlySalary() AS [Yearly Salary];
    GO
  13. All computers: Press F5 to execute
 
 
 

Functions 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
Deleting a Function

Practical LearningPractical Learning: Deleting a Function

  1. Server: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.CalculateDailySalary and click Delete
  2. pkatts: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.CalculateWeeklySalary and click Delete
  3. gmonay: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.EvaluateYearlySalary and click Delete
  4. rkouma: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.EvaluateWeeklySalary and click Delete
  5. operez: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.GetMonthlySalary and click Delete
  6. All Computers: In the Delete Object dialog box, click OK

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 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 LearningPractical Learning: Declaring Local Variables

  1. All Computers: Click inside the Query window and press Ctrl + A
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. All Computers: Press F5 to execute the statement
     
    Function
  8. All Computers: Click inside the Query window and press Ctrl + A
  9. Server: To call the function, type the following code:
    SELECT BethesdaCarRental1.Payroll.CalculateDailySalary()
    AS [Daily Salary];
    GO
  10. pkatts: Type:
    SELECT BethesdaCarRental1.Payroll.CalculateWeeklySalary()
    AS [Weekly Salary];
    GO
  11. gmonay: Type:
    SELECT BethesdaCarRental1.Payroll.EvaluateWeeklySalary()
    AS [Weekly Salary];
    GO
  12. rkouma: Type:
    SELECT BethesdaCarRental1.Payroll.GetMonthlySalary()
    AS [Monthly Salary];
    GO
  13. operez: Type:
    SELECT BethesdaCarRental1.Payroll.EvaluateYearlySalary()
    AS [Weekly Salary];
    GO
  14. All Computers: Execute the code by pressing F5

Functions Parameters

 

Introduction

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.

A Parameterized Function

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
Functions and Parameters

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:

Function

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:

Function

Practical LearningPractical Learning: Creating Functions With Arguments

  1. Server: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.CalculateDailySalary and click Delete
  2. pkatts: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.GetMonthlySalary and click Delete
  3. gmonay: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.EvaluateYearlySalary and click Delete
  4. rkouma: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.EvaluateWeeklySalary and click Delete
  5. operez: In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.CalculateWeeklySalary and click Delete
  6. All Computers: In the Delete Object dialog box, click OK
  7. All Computers: Click inside the Query window and press Ctrl + A
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. All Computers: Press F5 to create the function
  14. All Computers: Click inside the top section of the Query window and press Ctrl + A
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. All Computers: Press F5 to execute the statement
  21. All Students: Close Microsoft SQL Server
  22. All Students: When asked whether you want to save, click No
  23. Server:

Default Arguments

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.

Default Arguments

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:

Database Properties

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:

Function Error

 

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
Introduction to Functions

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:

Function Error

Practical LearningPractical Learning: Ending the Lesson

  1. Server: Launch Microsoft SQL Server and click Connect
  2. Server: In the Object Explorer, expand Databases
  3. Server: Under the Databases node, right-click BethesdaCarRental1 and click Delete
  4. Server: In the dialog box, click OK
  5. Server: Close Microsoft SQL Server
 
 
   
 

Previous Copyright © 2007-2011 FunctionX.com Next