Home

Introduction to SQL Functions

 

Functions Fundamentals

 
 

Introduction

Many languages support functions, including Visual Basic, C/C++, and Pascal, etc. As seen in the Visual Basic language, a function is a relatively small task that should be performed aside but can be accessed any time to give a result. In Transact-SQL, a function is considered an object. Based on this, you must create a function and execute it before using it. The function then becomes part of a database and it can be accessed like an object.

Practical LearningPractical Learning: Introducing Functions

  1. Start Microsoft Visual Basic and create a Windows Application named TriStateUtilityCompany1
  2. Design the form as follows:
     
    Functions
    Control Text Name Other Properties
    Label Customer Name    
    TextBox   txtCustomerName  
    Label Counter: ___________    
    Label Last Month:    
    TextBox   txtLastMonth TextAlign: Right
    Label This Month:    
    TextBox   txtThisMonth TextAlign: Right
    Label Consumption:    
    TextBox   txtConsumption TextAlign: Right
    Button Evaluate btnEvaluate  
    Label Invoice: ___________    
    Label Amount Due:    
    TextBox   txtAmountDue TextAlign: Right
    Label Database: _________    
    Button Database btnDatabase  
    Button Close btnClose  
  3. Double-click the Database button and implement its event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Exercise
    
        Private Sub btnDatabase_Click(ByVal sender As System.Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles btnDatabase.Click
            Dim strConnection As String = _
             "Data Source=(local);Integrated Security=yes"
    
            Using connection As SqlConnection = _
                	New SqlConnection(strConnection)
                Dim command As SqlCommand = _
             	New SqlCommand("CREATE DATABASE UtilityCompany1;", _
              			connection)
    
                connection.Open()
                command.ExecuteNonQuery()
    
                MsgBox("A database named ""UtilityCompany1"" has been created.")
            End Using
        End Sub
    End Class
  4. In the Class Name combo box, select btnClose
  5. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnClose_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnClose.Click
            End
    End Sub
     
  6. Execute the application
  7. Click the Database button
  8. Close the form and return to your programming environment

Function Creation Fundamentals

There are various ways you can create a function:

  • In the Object Explorer, you can expand the desired database,  expand the Programmatically node, and expand the Functions node. Right-click Scalar-Valued Function and click New Scalar-Valued Function... Sample code would be generated for you. You can then modify to customize it
  • Open an empty query window. Display the Templates Explorer and expand the Function node. Drag Create Scalar-Valued Function and drop it in the query window
  • You can open a new empty query window and start typing your code in it
  • Programmatically, you can include of a function creation in the command

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

CREATE FUNCTION FunctionName()

The Name of a Function

We mentioned already that, in SQL, a function was 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:

  • The name of a function will resemble an action. An example is calculate
  • A name will start with either an underscore or a letter. Examples are _n, act, or Perform
  • After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are _n24 or act_52_t
  • 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. Examples are DoSomething, _CreateStudentsRecords, Get_Age, or _Calculate_Volume_Area

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 varchar(100)
AS
BEGIN
	RETURN 'Doe, John'
END

Practical LearningPractical Learning: Creating a Function

  1. Change the code of the Database button as follows:
     
    Private Sub btnDatabase_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDatabase.Click
        Dim strConnection As String = _
      	"Data Source=(local);" & _
      	"Database='UtilityCompany1';" & _
      	"Integrated Security=yes;"
        Dim CreateFunction As String = "CREATE FUNCTION EvaluateInvoice() " & _
        			           "RETURNS Decimal(8, 2) " & _
               			   "AS " & _
               			   "BEGIN " & _
               			   "    RETURN 8.50 " & _
               			   "END;"
    
        Using connection As SqlConnection = _
    	     New SqlConnection(strConnection)
            Dim command As SqlCommand = _
         		New SqlCommand(CreateFunction, connection)
    
            connection.Open()
            Command.ExecuteNonQuery()
            MsgBox("A function named ""EvaluateInvoice"" has been created.")
        End Using
    End Sub
  2. Execute the application
  3. To actually create the function, click the Database button
     
    Creating a Function
  4. Close the form and return to your programming environment

Function Calling

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.

Practical Learning Practical Learning: Calling a Function

  1. In the Class Name combo box, select btnEvaluate
  2. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnEvaluate_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnEvaluate.Click
        Dim strConnection As String = _
             "Data Source=(local);" & _
             "Database='UtilityCompany1';" & _
             "Integrated Security=yes;"
        Dim ExecuteFunction As String = "SELECT dbo.EvaluateInvoice();"
    
        using connection As SqlConnection  = New SqlConnection(strConnection)
            Dim command As SqlCommand = _
    	     New SqlCommand(ExecuteFunction, connection)
    
            connection.Open()
            Dim rdr As SqlDataReader = command.ExecuteReader()
    
            While rdr.Read()
                txtAmountDue.Text = rdr(0)
            End While
    
            rdr.Close()
        End Using
    End Sub
  3. Execute the application and click the Evaluate button:
     
    Calling a Function
  4. Close the form and return to your programming environment

Function Maintenance

 

Introduction

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

Renaming a Function

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

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

Deleting a Function

If you create a function and decide that you do not need it any more, you can delete it.

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

To programmatically delete a function:

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

Practical Learning Practical Learning: Deleting a Function

  1. Change the code of the Database button as follows:
     
    Private Sub btnDatabase_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDatabase.Click
        Dim strConnection As String = _
    	  	"Data Source=(local);" & _
    	  	"Database='UtilityCompany1';" & _
    		"Integrated Security=yes;"
        Dim CreateFunction As String = "DROP FUNCTION EvaluateInvoice;"
    
        Using connection As SqlConnection = New SqlConnection(strConnection)
            Dim command As SqlCommand = _
                 New SqlCommand(CreateFunction, connection)
    
            connection.Open()
            command.ExecuteNonQuery()
            MsgBox("A function named ""EvaluateInvoice"" has been deleted.")
        End Using
    End Sub
  2. Execute the application and click the Database button
     
    Deleting a Function
  3. Close the form and return to your programming environment

Modifying a Function

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

CREATE FUNCTION Addition()
RETURNS int
BEGIN
    RETURN 1
END

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

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

Function Arguments

 

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 does not 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:

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 and Calling a Function with Argument

  1. Change the code of the Database button as follows:
     
    Private Sub btnDatabase_Click(ByVal sender As System.Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles btnDatabase.Click
            Dim strConnection As String = _
      "Data Source=(local);" & _
      "Database='UtilityCompany1';" & _
      "Integrated Security=yes;"
            ' The following function is used to calculate the customer's next bill
            ' Some of the empty spaces, not required,
            ' are meant to make the code easier to read
            Dim CreateFunction As String = _
            "CREATE FUNCTION EvaluateInvoice(@Counter int) " & _
               "RETURNS decimal(6, 2) " & _
               "AS " & _
               "BEGIN " & _
               "	 DECLARE @BaseCharge money, " & _
               "	 	 @Counter0To50 int, " & _
               "	 	 @Counter50To150 int, " & _
               "	 	 @Counter150To200 int, " & _
               "	 	 @CounterOver200 int, " & _
               "	  	 @First50 decimal(6, 2), " & _
               "	 	 @FiftyTo150 decimal(6, 2), " & _
               "	 	 @OneFiftyTo200 decimal(6, 2) ," & _
               "	 	 @Over200 decimal(6, 2), " & _
               "	 	 @TotalCharge money; " & _
                        "" & _
               "	 SET @BaseCharge = 8.50; " & _
               "	 SET @Counter0To50 = 0; " & _
               "	 SET @Counter50To150 = 0; " & _
               "	 SET @Counter150To200 = 0; " & _
               "	 SET @CounterOver200 = 0; " & _
               "	 SET @First50 = 0.00; " & _
               "	 SET @FiftyTo150 = 0.00; " & _
               "	 SET @OneFiftyTo200 = 0.00; " & _
               "	 SET @Over200 = 0.00; " & _
               "	 SET @TotalCharge = 0.00; " & _
                        "" & _
               "	 IF @Counter <= 50 " & _
               "	     BEGIN" & _
               "	 	SET @Counter0To50 = @Counter; " & _
               "	 	SET @Counter50To150 = 0; " & _
               "	 	SET @Counter150To200 = 0; " & _
               "	 	SET @CounterOver200 = 0; " & _
               "	     END;" & _
                        "" & _
               "	 IF (@Counter > 50) AND (@Counter <= 150) " & _
               "	     BEGIN" & _
               "	 	SET @Counter0To50 = 50; " & _
               "	 	SET @Counter50To150 = @Counter - 50; " & _
               " 		SET @Counter150To200 = 0; " & _
               " 		SET @CounterOver200 = 0; " & _
               " 	     END;" & _
                        "" & _
               "	 IF (@Counter > 150) AND (@Counter <= 300) " & _
               "	     BEGIN " & _
               "		SET @Counter0To50 = 50; " & _
               " 		SET @Counter50To150 = 100; " & _
               " 		SET @Counter150To200 = @Counter - 150; " & _
               " 		SET @CounterOver200 = 0; " & _
               " 	     END; " & _
                        "" & _
               " 	IF @Counter > 300 " & _
               "        BEGIN " & _
               " 		SET @Counter0To50 = 50; " & _
               " 		SET @Counter50To150 = 100; " & _
                 " 		SET @Counter150To200 = 100; " & _
               " 		SET @CounterOver200 = @Counter - 300; " & _
               "        END;" & _
                        "" & _
               " 	SET @First50 = @Counter0To50 * 0.7675; " & _
               " 	SET @FiftyTo150 = @Counter50To150 * 0.6248; " & _
               " 	SET @OneFiftyTo200 = @Counter150To200 * 0.5825; " & _
               " 	SET @Over200 = @CounterOver200 * 0.5037; " & _
                        "" & _
          " SET @TotalCharge = @BaseCharge + @First50 + @FiftyTo150 & " & _
               " 	@OneFiftyTo200 + @Over200; " & _
                    "" & _
               " 	RETURN @TotalCharge; " & _
               "END;"
    
            Using connection As SqlConnection = _
         New SqlConnection(strConnection)
                Dim command As SqlCommand = _
      New SqlCommand(CreateFunction, connection)
    
                connection.Open()
                Command.ExecuteNonQuery()
    
                MsgBox("A function named ""EvaluateInvoice"" has been created.")
            End Using
    End Sub
  2. Change the code of the Evaluate button as follows:
     
    Private Sub btnEvaluate_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnEvaluate.Click
        Dim StartCounter As Integer, EndCounter As Integer
        Dim Consumption As Integer
    
        Try
            StartCounter = CInt(txtCounterLastMonth.Text)
        Catch
            MsgBox("Invalid Start Counter")
        End Try
    
        Try
            EndCounter = CInt(txtCounterThisMonth.Text)
        Catch
            MsgBox("Invalid End Counter")
        End Try
    
        If StartCounter > EndCounter Then
            MsgBox("Invalid Values")
            Exit Sub
        End If
    
        Consumption = EndCounter - StartCounter
    
        Dim strConnection As String = _
         		"Data Source=(local);" & _
         		"Database='UtilityCompany1';" & _
         		"Integrated Security=yes;"
        Dim ExecuteFunction As String = "DECLARE @Difference int " & _
          		"SET @Difference = " & Consumption.ToString() & _
          		"SELECT dbo.EvaluateInvoice(@Difference);"
    
        Using connection As SqlConnection = New SqlConnection(strConnection)
            Dim command As SqlCommand = _
                 New SqlCommand(ExecuteFunction, connection)
    
            connection.Open()
            Dim rdr As SqlDataReader = command.ExecuteReader()
    
            txtConsumption.Text = Consumption.ToString()
    
            While rdr.Read()
                txtAmountDue.Text = rdr(0)
             End While
            rdr.Close()
        End Using
    End Sub
  3. Execute the application
  4. Click the Database button to create the function and click OK
  5. Enter some values for the start and end counter
  6. Click Evaluate. Here is an example:
     
    Creating and Calling a Function with Argument
  7. Close the form and return to your programming environment
  8. Change the code of the Database button as follows:
     
    Private Sub btnDatabase_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDatabase.Click
        Dim strConnection As String = _
            "Data Source=(local);Integrated Security=yes"
    
        Using connection As SqlConnection = New SqlConnection(strConnection)
            Dim command As SqlCommand = _
    	        New SqlCommand("DROP DATABASE UtilityCompany1;", _
            		       connection)
    
            connection.Open()
            Command.ExecuteNonQuery()
            MsgBox("The UtilityCompany1 database has been deleted.")
        End Using
    End Sub
  9. Execute the application
  10. Click the Database button
  11. Close the form and return to your programming environment

Introduction to Built-Functions

 

Overview

While your primary job as a database developer consists of creating lists, probably your second most important job is to assist your users with the various assignments they must perform on your application. One way you can assist is to use functions that perform otherwise complex tasks. To assist your development with the different tasks of a database, Transact-SQL ships with various already created and tested functions. You just need to be aware of these functions, their syntax, and the results they produce.

Because of their complexities, some values can be easily recognized or fixed. For example, a date such as January 6, 1995 is constant and can never change. This type of value is referred to as deterministic because it is always the same. In the same way, a time value such as 5 PM is constant and cannot change. There are other values that cannot be known in advance because they change based on some circumstances. For example, the starting date of the school year changes from one year to another but it always occurs. This means that, you know it will happen but you do not know the exact date. Such a value is referred to as non-deterministic.

To support determinism and non-determinism, Transact-SQL provides two broad categories of functions. A function that always returns the same or known value is referred to as deterministic. A function whose returned value may depend on a condition is referred to as non-deterministic.

Practical LearningPractical Learning: Introducing Built-in Functions

  1. Create a new Windows Application named Payroll
  2. From the Common Controls section of the Toolbox, add a Button to the form
  3. Double-click the button and implement its Click event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Exercise
    
        Private Sub Button1_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles Button1.Click
            Dim strConnection As String = _
     		"Data Source=(local);Integrated Security=yes"
    
            Using connection As SqlConnection = New SqlConnection(strConnection)
                Dim command As SqlCommand = _
                    New SqlCommand("CREATE DATABASE Payroll;", connection)
    
                connection.Open()
                command.ExecuteNonQuery()
    
                MsgBox("A database named ""Payroll"" has been created.")
            End Using
        End Sub
    End Class
  4. Execute the application
  5. Click the button
  6. Click OK
  7. Close the form and return to your programming environment
  8. Delete the button

Casting a Value

In most cases, a value the user submits to your database is primarily considered a string. This is convenient if that's what you are expecting. If the value the user provides must be treated as something other than a string, for example, if the user provides a number, before using such a value, you should first convert it to the appropriate type, that is, from a string to the expected type.

To assist with conversion, you can use either the CAST() or the CONVERT() function. The  syntax of the CAST() function is:

CAST(Expression AS DataType)

The Expression is the value that needs to be cast. The DataType factor is the type of value you want to convert the Expression to. The DataType can be one of those we reviewed in Lesson 20.

In the following example, two variables are declared and initialzed as strings. Because they must be involved in a multiplication, each is converted to a Decimal type.

Practical LearningPractical Learning: Casting a Value

  1.  Design the form as follows:
     
    Casting a Value
    Control Text Name Other Properties
    Label Hourly Salary:    
    TextBox 0.00 txtHourlySalary TextAlign: Right
    Label Weekly Hours:    
    TextBox 0.00 txtWeeklyHours TextAlign: Right
    Button Calculate btnCalculate  
    Label Weekly Salary:    
    TextBox 0.00 txtWeeklySalary TextAlign: Right
    Button Close btnClose  
  2. Double-click the Calculate button and implement the Click event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Exercise
    
        Private Sub btnCalculate_Click(ByVal sender As System.Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles btnCalculate.Click
            Dim HourlySalary As Double
            Dim WeeklyHours As Double
    
            Try
                HourlySalary = CDbl(txtHourlySalary.Text)
            Catch
                MsgBox("Invalid Weekly Hours")
            End Try
    
            Try
                WeeklyHours = CDbl(txtWeeklyHours.Text)
            Catch
                MsgBox("Invalid Weekly Hours")
            End Try
    
            Dim strConnection As String = _
         		"Data Source=(local);" & _
         		"Database='Payroll';" & _
         		"Integrated Security=yes;"
            Dim ExecuteFunction As String = _
             	"DECLARE @StrSalary Varchar(10), " & _
             	"@StrHours Varchar(6), " & _
             	"@WeeklySalary Decimal(6,2) " & _
             	"SET @StrSalary = '" & HourlySalary.ToString() & "'; " & _
             	"SET @StrHours = '" & WeeklyHours.ToString() & "'; " & _
             "SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) * " & _
             	"CAST(@StrHours As Decimal(6,2)); " & _
             	"SELECT @WeeklySalary;"
    
            Using connection As SqlConnection = New SqlConnection(strConnection)
                Dim command As SqlCommand = _
       		New SqlCommand(ExecuteFunction, connection)
    
                connection.Open()
                Dim rdr As SqlDataReader = Command.ExecuteReader()
    
                While rdr.Read()
                    txtWeeklySalary.Text = rdr(0)
                End While
    
                rdr.Close()
            End Using
        End Sub
    End Class
  3. Execute the application
  4. Enter a decimal value for the side and click the Calculate button. Here is an example:
     
    Casting a Value
  5. Close the form and return to your programming environment
  6. In the Class Name combo box, select btnClose
  7. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnClose_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnClose.Click
        Dim strConnection As String = _
    	   "Data Source=(local);Integrated Security=yes"
    
        Using connection As SqlConnection = New SqlConnection(strConnection)
            Dim command As SqlCommand = _
                    New SqlCommand("DROP DATABASE Payroll;", connection)
    
            connection.Open()
            command.ExecuteNonQuery()
            MsgBox("A database named ""Payroll"" has been deleted.")
        End Using
    End Sub
  8. Execute the application
  9. Click the Close button 
  10. Close the form and return to your programming environment
  11. Change the code of the Close button as follows:
     
    Private Sub btnClose_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnClose.Click
        End
    End Sub
  12. Save all

Converting a Value

Like CAST(), the CONVERT() function is used to convert a value. Unlike CAST(), CONVERT can be used to convert a value its original type into a non-similar type. For example, you can use CONVERT to cast a number into a string and vice-versa.

The  syntax of the CONVERT() function is:

CONVERT(DataType [ ( length ) ] , Expression [ , style ])

The first argument must be a known data type, such as those we reviewed in Lesson 4. If you are converting the value into a string (varchar, nvarchar, char, nchar) or a binary type, you should specify the number of allowed characters the data type's own parentheses. As reviewed for the CAST() function, the Expression is the value that needs to be converted.

Practical LearningPractical Learning: Converting a Value

  1. Start a new Windows Application named Square1
  2. Design the form as follows:
     
    Casting a Value
    Control Text Name Other Properties
    Label Side:    
    TextBox 0.00 txtSide TextAlign: Right
    Button Calculate btnCalculate  
    Label Perimeter:    
    TextBox 0.00 txtPerimeter TextAlign: Right
    Button Close btnClose  
    Label Area:    
    TextBox 0.00 txtArea TextAlign: Right
  3. Right-click the form and click View Code and implement its Load event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Execute
        Private Sub Execute_Load(ByVal sender As Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles Me.Load
            Dim strConnection As String = _
        		"Data Source=(local);Integrated Security=yes"
    
            Using connection As SqlConnection = New SqlConnection(strConnection)
                Dim command As SqlCommand = _
                        New SqlCommand("CREATE DATABASE Geometry1;", connection)
    
                connection.Open()
                command.ExecuteNonQuery()
                MsgBox("A database named ""Geometry1"" has been created.")
            End Using
        End Sub
    End Class
  4. Execute the application to create the database
  5. Close the form and return to your programming environment
  6. Delete all the code inside the Load event
  7. In the Class Name combo box, select btnCalculate
  8. In the Method Name combo box, select Click implement the event as follows:
     
    Private Sub btnCalculate_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnCalculate.Click
        Dim dSide As Double
    
        Try
            dSide = CDbl(txtSide.Text)
        Catch
            MsgBox("Invalid Side Value")
        End Try
    
        Dim strConnection As String = _
        	  "Data Source=(local);" & _
       	  "Database='Geometry1';" & _
         	  "Integrated Security=yes;"
        Dim ExecuteFunction As String = _
        	    	"DECLARE @Side As Decimal(10,3), " & _
            	"        @Perimeter As Decimal(10,3), " & _
            	"        @Area As Decimal(10,3); " & _
            	"SET     @Side = " & dSide.ToString() & "; " & _
            	"SET     @Perimeter = @Side * 4; " & _
            	"SET     @Area = @Side * @Side; " & _
            	"SELECT CONVERT(varchar(10), @Perimeter, 10), " & _
            "       CONVERT(varchar(10), @Area, 10);"
    
        Using connection As SqlConnection = _
        		New SqlConnection(strConnection)
            Dim command As SqlCommand = _
                 New SqlCommand(ExecuteFunction, connection)
    
            connection.Open()
            Dim rdr As SqlDataReader = command.ExecuteReader()
    
            While rdr.Read()
                txtPerimeter.Text = rdr(0)
                txtArea.Text = rdr(1)
            End While
            rdr.Close()
        End Using
    End Sub
  9. Execute the application
  10. Enter a decimal value for the side and click the Calculate button. Here is an example:
     
    Casting a Value
  11. Close the form and return to your programming environment
  12. In the Class Name combo box, select btnClose
  13. In the Method Name combo box, select Click implement the event as follows:
     
    Private Sub btnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles btnClose.Click
        Dim strConnection As String = _
    	     "Data Source=(local);Integrated Security=yes"
    
        Using connection As SqlConnection = New SqlConnection(strConnection)
            Dim command As SqlCommand = _
                    New SqlCommand("DROP DATABASE Geometry1;", connection)
    
            connection.Open()
            command.ExecuteNonQuery()
            MsgBox("The ""Geometry1"" database named has been deleted.")
        End Using
    End Sub
  14. Execute the application and click the Close button to close it
  15. Close the form and return to your programming environment
  16. Change the code of the Close button as follows:
     
    Private Sub btnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles btnClose.Click
            End
    End Sub
  17. Save all

Lesson Summary

 

Exercises

  1. Write a function named ProcessPayroll1 that takes the number of hours worked in a week. Then the function returns a value that represents overtime. If the employee worked less than 40 hours, there is no overtime. If the employee worked for more than 40 hours, the number over 40 is considered overtime
  2. Write a function named GetWeekdayName that, when given a date, can find and display the name of the week for that date
  3. Write a function named GetNumberDays that takes two dates and returns the number of days between them
  4. Write a function named AddNumberDays that takes a date and an integer, then it returns the date added that number
 

Home Copyright © 2008-2012 FunctionX