Home

Introduction to Built-Functions
   

Introduction
 

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. We introduced and described functions in the previous lesson. 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 don't 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 Payroll10
  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:
     
    private void button1_Click(object sender, EventArgs e)
{
    string strConnection =
	"Data Source=(local);Integrated Security=yes";

    using (SqlConnection connection = new SqlConnection(strConnection))
    {
	SqlCommand command =
	    new SqlCommand("CREATE DATABASE Exercise1;", connection);

	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A database named \"Exercise4\" has been created.");
    }
}
  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
  3. Return to the form and double-click the Close button
  4. Implement the events as follows:
     
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exernamespace Payroll10
{
    public partial class Exercise : Form
    {
	public Exercise()
	{
	    InitializeComponent();
	}

    	private void btnCalculate_Click(object sender, EventArgs e)
	{
	    double HourlySalary = 0.00, WeeklyHours = 0.00;

	    try {
		HourlySalary = double.Parse(txtHourlySalary.Text);
	    }
	    catch(FormatException)
	    {
		MessageBox.Show("Invalid Weekly Hours");
	    }

	    try {
	    	WeeklyHours = double.Parse(txtWeeklyHours.Text);
	    }
	    catch(FormatException)
	    {
	    	MessageBox.Show("Invalid Weekly Hours");
	    }

	    string strConnection =
		"Data Source=(local);" +
		"Database='UtilityCompany1';" +
		"Integrated Security=yes;";
	    string ExecuteFunction =
		"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 (SqlConnection connection = new SqlConnection(strConnection))
	    {
	    	SqlCommand command =
			new SqlCommand(ExecuteFunction, connection);

	    	connection.Open();
	    	SqlDataReader rdr = command.ExecuteReader();

	    	while (rdr.Read())
	    	{
		    txtWeeklySalary.Text = rdr[0].ToString();
	    	}

	    	rdr.Close();
	    }
    	}

    	private void btnClose_Click(object sender, EventArgs e)
    	{
	    Close();
    	}
    }
}
  5. Execute the application
  6. Enter a decimal value for the side and click the Calculate button. Here is an example:
     
    Casting a Value
  7. Close the form and return to your programming environment

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 Square10
  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. Double-click the Calculate button
  4. Return to the form and double-click the Close button
  5. Implement the events as follows:
     
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise1
{
    public partial class Exercise : Form
    {
	public Exercise()
	{
	    InitializeComponent();
	}

    	private void btnCalculate_Click(object sender, EventArgs e)
	{
	    double dSide = 0.00;

	    try {
		dSide = double.Parse(txtSide.Text);
	    }
	    catch(FormatException)
	    {
		MessageBox.Show("Invalid Side Value");
	    }

	    string strConnection =
		"Data Source=(local);" +
		"Database='UtilityCompany1';" +
		"Integrated Security=yes;";
	    string ExecuteFunction =
		"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 (SqlConnection connection =
		new SqlConnection(strConnection))
	    {
		SqlCommand command =
			new SqlCommand(ExecuteFunction, connection);

		connection.Open();
		SqlDataReader rdr = command.ExecuteReader();

		while (rdr.Read())
		{
		    txtPerimeter.Text = rdr[0].ToString();
		    txtArea.Text = rdr[1].ToString();
		}

		rdr.Close();
	    }
    	}

    	private void btnClose_Click(object sender, EventArgs e)
    	{
	    Close();
    	}
    }
}
  6. Execute the application
  7. Enter a decimal value for the side and click the Calculate button. Here is an example:
     
    Casting a Value
  8. Close the form and return to your programming environment
 

Published on Monday 27 December 2007

 
Home Copyright © 2007 FunctionX, Inc. Home