Functions Fundamentals

Introduction

A function is a section of code that is used to solve a certain problem. Once one a function has performed that job, other parts of a program can refer to hat function to get the effect(s) of the result(s) of that operation. As it happens, functions can be used to perform various types of operations.

In Transact-SQL, a function is considered an object.

Practical Learning: Introducing Functions

1. Start Microsoft SQL Server and click Connect
2. To create a new database, right-click Databases and click New Database...
3. In the Database Name text box, type WaterCompany1
4. Click OK
5. In the Object Explorer, expand Databases (or right-click Databases and click Refresh)
6. In the Object Explorer, expand WaterCompany1
7. Under WaterCompany1, right-click Security, position the mouse on New, and click Schema...
8. In the Schema Name, type Payroll
9. Click OK

Creating a Function

There are various ways you can start the creation of a function. If you already know the code necessary to create a function, open a new empty Query Editor and type the code to create a function.

A Schema for a Function

A function must belong to a schema; that is, a schema must own a function. When creating a function, you should specify its schema. If you don't specify the schema, the function is automatically considered as being created by, and belong to, the dbo schema. Otherwise, before creating a function, first create a schema. Then when creating the function, you can specify its schema. As a result, to create 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...

  

  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 Editor. Display the Template Explorer window and expand the Function node. Drag Create Scalar Function (New Menu):

  

  and drop it in the Query Editor

  -- ================================================
  -- 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

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

CREATE FUNCTION schema-name.function-name()

The Name of a Function We already mentioned 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 After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits A name will not include special characters such as !, @, #, $, %, ^, &, or * We will avoid using spaces in a name If the name is a combination of words, each word will start in uppercase

Returning a Value From a Function

In some cases, a function can 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 Administration.Addition()
RETURNS Decimal(6,3)

The Body of a Function

A function must have a body. The body of a function is the section of code that defines what the function is supposed to do. To create or specify the body of the function, after its return type, on the next line, type BEGIN, and on the next line, type END. Here is an example:

CREATE FUNCTION Administration.Addition()
RETURNS Decimal(6,3)
BEGIN

END

Optionally, you can type the AS keyword before the BEGIN keyword:

CREATE FUNCTION Administration.Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END

Between the BEGIN and END keywords, you can define the function. After performing the operation(s) of the function, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by a a value or an expression. A sample formula is:

CREATE FUNCTION Administration.Addition()
RETURNS Decimal(6,3)
AS
BEGIN
    RETURN expression
END

Here is an example

CREATE FUNCTION GetFullName()
RETURNS nvarchar(22)
AS
BEGIN
	RETURN N'Doe, John'
END

Practical Learning: Creating a Function

1. In the Object Explorer, right-click WaterCompany1 and click New Query
2. To create a function, type the following statement:

   USE WaterCompany1;
   GO
   
   CREATE FUNCTION Payroll.CalculateWeeklySalary()
   RETURNS Decimal(8, 2)
   AS
   BEGIN
       RETURN 880.44
   END;
   GO

3. To execute the statement, on the SQL Editor toolbar, click the Execute button
4. In the Object Explorer, below the WaterDistribution1 database, expand Programmability, expand Functions, expand Scalar-Valued Functions. Notice the presence of the CalculateWeeklySalary node

Calling a Function

SchemaName.FunctionName()

DatabaseName.SchemaName.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 the Query Editor. Here is an example:

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.

Practical Learning: Calling a Function

1. Click inside the Query Editor and press Ctrl + A to select everything
2. To execute the function we just created, type the following:

   PRINT WaterCompany1.Payroll.CalculateWeeklySalary();
   GO

3. To execute, press F5
4. To specify a caption for the returned value of a function, change the function call as follows:

   SELECT WaterCompany1.Payroll.CalculateWeeklySalary() AS [Weekly Salary];
   GO

5. 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 visually 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 Editor, 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 Editor 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 Editor:

Practical Learning: Deleting a Function

1. In the Object Explorer, under the Scalar-Valued Functions node, right-click Payroll.CalculateWeeklySalary and click Delete
2. In the Delete Object dialog box, click OK

A Function's Local Variables

In the body of the function, you can declare one or more variable. 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 Administration.Addition()
RETURNS int
BEGIN
    DECLARE @Number int
    SET @Number = 588
    RETURN @Number + 1450
END

Practical Learning: Declaring Local Variables

1. Click inside the Query Editor and press Ctrl + A
2. To create a new function, type the following code:

   CREATE FUNCTION Payroll.CalculateWeeklySalary()
   RETURNS Decimal(8, 2)
   AS
   BEGIN
   	DECLARE
   		@HourlySalary Decimal(8, 2),
   		@WeeklyHours Real,
   		@FullName varchar(100);
   		SET @HourlySalary = 24.15;
   		SET @WeeklyHours = 42.50;
   	RETURN @HourlySalary * @WeeklyHours
   END;
   GO

3. Press F5 to execute the statement
4. Click inside the Query Editor and press Ctrl + A
5. To call the function, type the following code:

   SELECT Payroll.CalculateWeeklySalary()
   AS [Weekly Salary];
   GO

6. Execute the code by pressing F5

Functions Parameters Introduction In order to perform its operation, a function can be provided with some values. 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 preceded by the @ sign, like a variable. Here is an example:

CREATE FUNCTION Administration.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 Administration.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 parameters in the parentheses of the function with a comma. Here is an example:

CREATE FUNCTION Administration.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 Administration.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 Exercise.Administration.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,
       Exercise.Administration.Addition(@Nbr1, @Nbr2) AS Result

This would produce:

Practical Learning: Creating Functions With Arguments

1. Click inside the Query Editor and press Ctrl + A
2. To add arguments, type the following code:

   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

3. Press F5 to create the function

   

4. Delete the code in the window and replace it with the following:

   DECLARE @Hours Decimal(5,2),
           @Hourly SmallMoney
   SELECT  @Hours = 42.50
   SELECT  @Hourly = 18.62
   SELECT  'Hermine Singh' As [Employee Name],
           @Hours As [Weekly Hours],
           @Hourly As [Hourly Salary],
           RealEstate1.Payroll.CalculateWeeklySalary(@Hours, @Hourly)
           	AS [Weekly Salary];
   GO

5. Press F5 to execute the statement

   

6. Close tMicrosoft SQL Server
7. When asked whether you want to save, click No

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 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

Practical Learning: Ending the Lesson

1. Click inside the Query Editor and press Ctrl + A
2. Type the following:

   USE master;
   GO
   DROP DATABASE IceCreamShop;
   GO
   DROP DATABASE University1;
   GO

3. Press F5 to execute
4. In the Object Explorer, below WaterCompany1, below Programmatically, below Functions, right-click Scalar-Valued Function and click New Scalar-Valued Function...
5. Change the code as follows:

   USE WaterCompany1;
   GO
   
   CREATE SCHEMA Management;
   GO
   -- ================================================
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   -- =============================================
   CREATE FUNCTION Management.DefineAccount 
   (
   	@type nchar(3)
   )
   RETURNS NVARCHAR(120)
   AS
   BEGIN
   	DECLARE @ResultVar NVARCHAR(120);
   
   	IF @type = N'RES'
   		SET @ResultVar = N'RES - Residential Household';
   	ELSE IF @type = N'SGO'
   		SET @ResultVar = N'SGO - Social/Government/Non-Profit Organization';
   	ELSE IF @type = N'BUS'
   		SET @ResultVar = N'BUS - General Business';
   	ELSE IF @type = N'UUO'
   		SET @ResultVar = N'UUO - Unidentified or Unclassified Type of Organization';
   	ELSE IF @type = N'WAT'
   		SET @ResultVar = N'WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc';
   	ELSE
   	    SET @ResultVar = N'OTH - Other';
   
   	RETURN @ResultVar
   END
   GO

6. Open an empty Query Editor. Display the Template Explorer window and expand the Function node. Drag Create Scalar Function (New Menu):

   

   and drop it in the Query Editor

   USE WaterCompany1;
   GO
   
   CREATE SCHEMA Management;
   GO
   -- ================================================
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   -- =============================================
   CREATE FUNCTION Management.DefineAccount
   (
   	@type nchar(3)
   )
   RETURNS NVARCHAR(120)
   AS
   BEGIN
   	DECLARE @result NVARCHAR(120);
   
   	IF @type = N'RES'
   		SET @result = N'RES - Residential Household';
   	ELSE IF @type = N'SGO'
   		SET @result = N'SGO - Social/Government/Non-Profit Organization';
   	ELSE IF @type = N'BUS'
   		SET @result = N'BUS - General Business';
   	ELSE IF @type = N'UUO'
   		SET @result = N'UUO - Unidentified or Unclassified Type of Organization';
   	ELSE IF @type = N'WAT'
   		SET @result = N'WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc';
   	ELSE
   	    SET @result = N'OTH - Other';
   
   	RETURN @result
   END
   GO

7. To formally create the function, on the main menu, click Query -> Execute
8. If the Template Browser is not displaying, on the main menu, click View -> Template Explorer.
   In the Template Browser, expand Function
9. From the Template Browser, below Function, drag Create Scalar Function (new Menu) and drop it in the Query Editor
10. Change the code as follows:

    USE WaterCompany1;
    GO
    
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    CREATE FUNCTION Management.CalculateFirstTierConsumption 
    (
    	@type nchar(3), @total decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2)
    
    	IF @type = N'RES'
    		SET @result = @total * 41.50 / 10000.00;
    	ELSE IF @type = N'SGO'
    		SET @result = @total * 46.00 / 10000.00;
    	ELSE IF @type = N'BUS'
    		SET @result = @total * 45.00 / 10000.00;
    	ELSE IF @type = N'UUO'
    		SET @result = @total * 25.00 / 10000.00;
    	ELSE IF @type = N'WAT'
    		SET @result = @total * 50.00 / 10000.00;
    	ELSE
    		SET @result = @total * 48.00 / 10000.00;
    
    	RETURN @result;
    END
    GO

11. To create the function, on the main menu, click Query -> Execute
12. Click inside the Query Editor and press Ctrl + A
13. To create other functions, type the following code:

    USE WaterCompany1;
    GO
    
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    CREATE FUNCTION Management.CalculateSecondTierConsumption 
    (
    	@type nchar(3), @total decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    		SET @result = @total * 32.50 / 10000.00;
    	ELSE IF @type = N'SGO'
    		SET @result = @total * 50.00 / 10000.00;
    	ELSE IF @type = N'BUS'
    		SET @result = @total * 45.00 / 10000.00;
    	ELSE IF @type = N'UUO'
    		SET @result = @total * 35.00 / 10000.00;
    	ELSE IF @type = N'WAT'
    		SET @result = @total * 40.00 / 10000.00;
    	ELSE
    	    SET @result = @total * 32.00 / 10000.00;
    
    	RETURN @result
    END
    GO
    
    CREATE FUNCTION Management.CalculateLastTierConsumption 
    (
    	@type nchar(3), @total decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    		SET @result   = @total * 26.00 / 10000.00;
    	ELSE IF @type = N'SGO'
    		SET @result   = @total * 4.00 / 10000.00;
    	ELSE IF @type = N'BUS'
    		SET @result   = @total * 25.00 / 10000.00;
    	ELSE IF @type = N'UUO'
    		SET @result   = @total * 40.00 / 10000.00;
    	ELSE IF @type = N'WAT'
    		SET @result   = @total * 10.00 / 10000.00;
    	ELSE
    	    SET @result   = @total * 20.00 / 10000.00;
    
    	RETURN @result
    END
    GO
    
    CREATE FUNCTION Management.CalculateSewerCharges 
    (
    	@type nchar(3), @charges decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    		SET @result = @charges * 6.826941 / 100;
    	ELSE IF @type = N'SGO'
    		SET @result = @charges * 4.162522 / 100;
    	ELSE IF @type = N'BUS'
    		SET @result = @charges * 8.315136 / 100;
    	ELSE IF @type = N'UUO'
    		SET @result = @charges * 10.626147 / 100;
    	ELSE IF @type = N'WAT'
    		SET @result = @charges * 12.025135 / 100;
    	ELSE
    	    SET @result = @charges * 9.202615 / 100;
    
    	RETURN @result
    END
    GO
    
    CREATE FUNCTION Management.CalculateEnvironmentCharges
    (
    	@type nchar(3), @charges decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    		SET @result = @charges * 0.022724;
    	ELSE IF @type = N'SGO'
    		SET @result = @charges * 0.118242;
    	ELSE IF @type = N'BUS'
    		SET @result = @charges * 0.161369;
    	ELSE IF @type = N'UUO'
    		SET @result = @charges * 0.082477;
    	ELSE IF @type = N'WAT'
    		SET @result = @charges * 0.413574;
    	ELSE
    	    SET @result = @charges * 0.221842;
    
    	RETURN @result
    END
    GO
    
    CREATE FUNCTION Management.CalculateServiceCharges
    (
    	@type nchar(3), @charges decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    	    SET @result = @charges * 0.145748;
    	ELSE IF @type = N'SGO'
    		SET @result = @charges * 0.102246;
    	ELSE IF @type = N'BUS'
    		SET @result = @charges * 0.242627;
    	ELSE IF @type = N'UUO'
    		SET @result = @charges * 0.186692;
    	ELSE IF @type = N'WAT'
    		SET @result = @charges * 0.412628;
    	ELSE
    		SET @result = @charges * 0.210248;
    
    	RETURN @result
    END
    GO
    
    CREATE FUNCTION Management.CalculateLocalTaxes
    (
    	@type nchar(3), @total decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    		SET @result = @total * 0.031574;
    	ELSE IF @type = N'SGO'
    		SET @result = @total * 0.035026;
    	ELSE IF @type = N'BUS'
    		SET @result = @total * 0.122517;
    	ELSE IF @type = N'UUO'
    		SET @result = @total * 0.105737;
    	ELSE IF @type = N'WAT'
    		SET @result = @total * 0.153248;
    	ELSE
    	    SET @result = @total * 0.125148;
    
    	RETURN @result
    END
    GO
    
    CREATE FUNCTION Management.CalculateStateTaxes
    (
    	@type nchar(3), @total decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    		SET @result = @total * 0.016724;
    	ELSE IF @type = N'SGO'
    	    SET @result = @total * 0.008779;
    	ELSE IF @type = N'BUS'
    		SET @result = @total * 0.042448;
    	ELSE IF @type = N'UUO'
    		SET @result = @total * 0.067958;
    	ELSE IF @type = N'WAT'
    		SET @result = @total * 0.081622;
    	ELSE
    		SET @result = @total * 0.013746;
    
    	RETURN @result
    END
    GO
    
    CREATE FUNCTION Management.CalculateLateAmountDue
    (
    	@type nchar(3), @amt decimal(8, 2)
    )
    RETURNS decimal(8, 2)
    AS
    BEGIN
    	DECLARE @result decimal(8, 2);
    
    	IF @type = N'RES'
    		SET @result = @amt + 8.95;
    	ELSE IF @type = N'SGO'
    		SET @result = @amt + (@amt / 4.575);
    	ELSE IF @type = N'BUS'
    		SET @result = @amt + (@amt / 12.315);
    	ELSE IF @type = N'UUO'
    		SET @result = @amt + (@amt / 7.425);
    	ELSE IF @type = N'WAT'
    		SET @result = @amt + (@amt / 15.225);
    	ELSE
    	    SET @result = @amt + (@amt / 6.735);
    
    	RETURN @result
    END
    GO

14. To create the function, on the main menu, click Query -> Execute
15. Click inside the Query Editor and press Ctrl + A
16. To test the functions, type the following code:

    USE WaterCompany1;
    GO
    
    -- Stellar Water Point
    /* Types of Accounts
    OTH - Other
    BUS - General Business
    RES - Residential Household
    SGO - Social/Government/Non-Profit Organization
    UUO - Unidentified or Unclassified Type of Organization
    WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc */
    
    DECLARE @acntNbr             NVarChar(15) = N'2068-258-9486';
    DECLARE @type                nchar(3) = N'UUO';
    DECLARE @counterReadingStart numeric  = 103943;
    DECLARE @counterReadingEnd   numeric  = 103956;
    
    DECLARE @consumption int = @counterReadingEnd - @counterReadingStart;
    DECLARE @HCFTotal numeric(10, 2) = @consumption * 748.05;
    DECLARE @gallons numeric = @consumption * 748.05;
    
    DECLARE @acntType NVARCHAR(120)            = Management.DefineAccount(@type);
    
    DECLARE @firstTier numeric(8, 2)           = Management.CalculateFirstTierConsumption(@type, @gallons);
    DECLARE @secondTier numeric(8, 2)          = Management.CalculateSecondTierConsumption(@type, @gallons);
    DECLARE @lastTier numeric(8, 2)            = Management.CalculateLastTierConsumption(@type, @gallons);
    
    DECLARE @waterCharges numeric(10, 2)       = @firstTier + @secondTier + @lastTier;
    
    DECLARE @sewerCharges numeric(10, 2)       = Management.CalculateSewerCharges(@type, @waterCharges);
    DECLARE @environmentCharges numeric(10, 2) = Management.CalculateEnvironmentCharges(@type, @waterCharges);
    DECLARE @serviceCharges numeric(10, 2)     = Management.CalculateServiceCharges(@type, @waterCharges);
    
    DECLARE @totalCharges numeric(10, 2)       = @waterCharges + @sewerCharges + @environmentCharges + @serviceCharges;
    
    DECLARE @localTaxes numeric(10, 2)         = Management.CalculateLocalTaxes(@type, @totalCharges);
    DECLARE @stateTaxes numeric(10, 2)         = Management.CalculateStateTaxes(@type, @totalCharges);
    
    DECLARE @amountDue numeric(10, 2) = @totalCharges + @localTaxes + @stateTaxes;
    
    DECLARE @lateAmountDue numeric(10, 2)     = Management.CalculateLateAmountDue(@type, @amountDue);
    
    PRINT '===================================================================================================';
    PRINT 'Stellar Water Point - Customer Invoice';
    PRINT '---------------------------------------------------------------------------------------------------';
    PRINT CONCAT('Account Number:             ', @acntNbr);
    PRINT CONCAT('Account Type:               ', @acntType);
    PRINT '===================================================================================================';
    PRINT 'Meter Reading';
    PRINT '---------------------------------------------------------------------------------------------------';
    PRINT CONCAT('Counter Reading Start:      ', @counterReadingStart);
    PRINT CONCAT('Counter Reading End:        ', @counterReadingEnd);
    PRINT CONCAT('Total Gallons Consumed:     ', @consumption);
    PRINT CONCAT('HCF Total                   ', @HCFTotal);
    PRINT CONCAT('Gallons                     ', @gallons);
    PRINT '===================================================================================================';
    PRINT 'Therms Evaluation';
    PRINT '---------------------------------------------------------------------------------------------------';
    PRINT CONCAT('First Tier Consumption:     ', @firstTier);
    PRINT CONCAT('Second Tier Consumption:    ', @secondTier);
    PRINT CONCAT('Last Tier Consumption:      ', @lastTier);
    PRINT '---------------------------------------------------------------------------------------------------';
    PRINT CONCAT('Water Use Charges:          ', @waterCharges);
    PRINT CONCAT('Sewer Charges:              ', @sewerCharges);
    PRINT '===================================================================================================';
    PRINT 'Bill Values';
    PRINT '---------------------------------------------------------------------------------------------------';
    PRINT CONCAT('Environment Charges:        ', @environmentCharges);
    PRINT CONCAT('Service Charges:            ', @serviceCharges);
    PRINT CONCAT('Total Charges:              ', @totalCharges);
    PRINT CONCAT('Local Taxes:                ', @localTaxes);
    PRINT CONCAT('State Taxes:                ', @stateTaxes);
    PRINT '---------------------------------------------------------------------------------------------------';
    PRINT CONCAT('Amount Due:                 ', @amountDue);
    PRINT CONCAT('Late Amount Due:            ', @lateAmountDue);
    PRINT '===================================================================================================';

17. To execute, on the main menu, click Query -> Execute:

    ===================================================================================================
    Stellar Water Point - Customer Invoice
    ---------------------------------------------------------------------------------------------------
    Account Number:             2068-258-9486
    Account Type:               UUO - Unidentified or Unclassified Type of Organization
    ===================================================================================================
    Meter Reading
    ---------------------------------------------------------------------------------------------------
    Counter Reading Start:      103943
    Counter Reading End:        103956
    Total Gallons Consumed:     13
    HCF Total                   9724.65
    Gallons                     9725
    ===================================================================================================
    Therms Evaluation
    ---------------------------------------------------------------------------------------------------
    First Tier Consumption:     24.31
    Second Tier Consumption:    34.04
    Last Tier Consumption:      38.90
    ---------------------------------------------------------------------------------------------------
    Water Use Charges:          97.25
    Sewer Charges:              10.33
    ===================================================================================================
    Bill Values
    ---------------------------------------------------------------------------------------------------
    Environment Charges:        8.02
    Service Charges:            18.16
    Total Charges:              133.76
    Local Taxes:                14.14
    State Taxes:                9.09
    ---------------------------------------------------------------------------------------------------
    Amount Due:                 156.99
    Late Amount Due:            178.13
    ===================================================================================================

18. In the code, change the value of the @acntNbr variable to 7518-302-6895
19. In the code, change the @type value to BUS
20. To execute, on the main menu, click Query -> Execute:

    ===================================================================================================
    Stellar Water Point - Customer Invoice
    ---------------------------------------------------------------------------------------------------
    Account Number:             7518-302-6895
    Account Type:               BUS - General Business
    ===================================================================================================
    Meter Reading
    ---------------------------------------------------------------------------------------------------
    Counter Reading Start:      103943
    Counter Reading End:        103956
    Total Gallons Consumed:     13
    HCF Total                   9724.65
    Gallons                     9725
    ===================================================================================================
    Therms Evaluation
    ---------------------------------------------------------------------------------------------------
    First Tier Consumption:     43.76
    Second Tier Consumption:    43.76
    Last Tier Consumption:      24.31
    ---------------------------------------------------------------------------------------------------
    Water Use Charges:          111.83
    Sewer Charges:              9.30
    ===================================================================================================
    Bill Values
    ---------------------------------------------------------------------------------------------------
    Environment Charges:        18.05
    Service Charges:            27.13
    Total Charges:              166.31
    Local Taxes:                20.38
    State Taxes:                7.06
    ---------------------------------------------------------------------------------------------------
    Amount Due:                 193.75
    Late Amount Due:            209.48
    ===================================================================================================

21. In the code, change the value of the @acntNbr variable to 7028-405-9381
22. In the code, change the @type value to WAT
23. To execute, on the main menu, click Query -> Execute:

    ===================================================================================================
    Stellar Water Point - Customer Invoice
    ---------------------------------------------------------------------------------------------------
    Account Number:             7028-405-9381
    Account Type:               WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc
    ===================================================================================================
    Meter Reading
    ---------------------------------------------------------------------------------------------------
    Counter Reading Start:      103943
    Counter Reading End:        103956
    Total Gallons Consumed:     13
    HCF Total                   9724.65
    Gallons                     9725
    ===================================================================================================
    Therms Evaluation
    ---------------------------------------------------------------------------------------------------
    First Tier Consumption:     48.63
    Second Tier Consumption:    38.90
    Last Tier Consumption:      9.73
    ---------------------------------------------------------------------------------------------------
    Water Use Charges:          97.26
    Sewer Charges:              11.70
    ===================================================================================================
    Bill Values
    ---------------------------------------------------------------------------------------------------
    Environment Charges:        40.22
    Service Charges:            40.13
    Total Charges:              189.31
    Local Taxes:                29.01
    State Taxes:                15.45
    ---------------------------------------------------------------------------------------------------
    Amount Due:                 233.77
    Late Amount Due:            249.12
    ===================================================================================================

24. In the code, change the value of the @acntNbr variable to 5938-074-5293
25. In the code, change the @type value to SGO
26. To execute, on the main menu, click Query -> Execute:

    ===================================================================================================
    Stellar Water Point - Customer Invoice
    ---------------------------------------------------------------------------------------------------
    Account Number:             5938-074-5293
    Account Type:               SGO - Social/Government/Non-Profit Organization
    ===================================================================================================
    Meter Reading
    ---------------------------------------------------------------------------------------------------
    Counter Reading Start:      103943
    Counter Reading End:        103956
    Total Gallons Consumed:     13
    HCF Total                   9724.65
    Gallons                     9725
    ===================================================================================================
    Therms Evaluation
    ---------------------------------------------------------------------------------------------------
    First Tier Consumption:     44.74
    Second Tier Consumption:    48.63
    Last Tier Consumption:      3.89
    ---------------------------------------------------------------------------------------------------
    Water Use Charges:          97.26
    Sewer Charges:              4.05
    ===================================================================================================
    Bill Values
    ---------------------------------------------------------------------------------------------------
    Environment Charges:        11.50
    Service Charges:            9.94
    Total Charges:              122.75
    Local Taxes:                4.30
    State Taxes:                1.08
    ---------------------------------------------------------------------------------------------------
    Amount Due:                 128.13
    Late Amount Due:            156.14
    ===================================================================================================

27. Change the value of the @acntNbr variable to 6003-386-3955 and press Enter
28. Change the value of the @type variable to Unknown and press Enter
29. To execute, on the main menu, click Query -> Execute:

    ===================================================================================================
    Stellar Water Point - Customer Invoice
    ---------------------------------------------------------------------------------------------------
    Account Number:             6003-386-3955
    Account Type:               OTH - Other
    ===================================================================================================
    Meter Reading
    ---------------------------------------------------------------------------------------------------
    Counter Reading Start:      103943
    Counter Reading End:        103956
    Total Gallons Consumed:     13
    HCF Total                   9724.65
    Gallons                     9725
    ===================================================================================================
    Therms Evaluation
    ---------------------------------------------------------------------------------------------------
    First Tier Consumption:     46.68
    Second Tier Consumption:    31.12
    Last Tier Consumption:      19.45
    ---------------------------------------------------------------------------------------------------
    Water Use Charges:          97.25
    Sewer Charges:              8.95
    ===================================================================================================
    Bill Values
    ---------------------------------------------------------------------------------------------------
    Environment Charges:        21.57
    Service Charges:            20.45
    Total Charges:              148.22
    Local Taxes:                18.55
    State Taxes:                2.04
    ---------------------------------------------------------------------------------------------------
    Amount Due:                 168.81
    Late Amount Due:            193.87
    ===================================================================================================
    
    Completion time: 2025-04-07T09:34:47.0653896-04:00

30. Close Microsoft SQL Server
31. When asked whether you want to save, click No