Home

Operations on SQL Variables

 

SQL Selection

 

Introduction to SQL Operators and Operands

An operation is an action performed on one or more values either to modify the value held by one or both of the variables or to produce a new value by combining values. Therefore, an operation is performed by using at least one symbol and one value. The symbol used in an operation is called an operator. A value involved in an operation is called an operand.

Practical LearningPractical Learning: Introducing Database Operations

  1. Start Microsoft Visual C# and create a Windows Application named Exercise4
  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 Exercise4;", 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 on the form and design it (the form) as follows:
     
    Seven-Locks Flower Shop - Employee Payroll
    Control Text Name Other Properties
    Label First Name:    
    TextBox   txtFirstName  
    Label Last Name:    
    TextBox   txtLastName  
    Label Full Name:    
    TextBox   txtFulName  
    Label Date Hired:    
    DateTimePicker   dtpDateHired  
    Label Employment Status:    
    ComboBox   cbxEmploymentStatus Items:
    Full Time
    Part Time
    Contractor
    Seasonal
    Intern
    Label Weekly Status:    
    TextBox   txtWeeklyStatus TextAlign: Right
    Label Hourly Salary:    
    TextBox   txtHourlyStatus TextAlign: Right
    Button Operate btnOperate  
    Label Weekly Salary:    
    TextBox   txtWeeklySalary TextAlign: Right
    Button Close btnClose  
  9. Save the form

PRINT Something

Like every language, SQL ships with some words used to carry its various operations. One of these words is PRINT. To display something in plain text as a result of a statement, type PRINT followed by what to display. Therefore, PRINT uses the following formula:

PRINT WhatToPrint

The item to display can be anything that is allowed and it is provided on the right side of PRINT. If it is a regular constant number, simply type it on the right side of PRINT. The item to display can also be an operation or the result of an operation. You can also display an expression as a combination of number(s) and sentences.

SELECT Something

The SELECT operator can be used, among other things, to display a value. The SELECT keyword uses the following syntax:

SELECT What

Based on this, to use it, where it is needed, type SELECT followed by a number, a word, a string, or an expression. The item to display follows some of the same rules as PRINT. One of the differences between PRINT and SELECT is that:

As done for PRINT, to display a sentence using SELECT, type it in single-quotes on the right side of this operator. Here is an executed example:

When you create a SELECT statement, what is on the right side of SELECT must be a value. Here is an example:

SELECT 226.75;

Based on this definition, instead of just being a value, the thing on the right side of SELECT must be able to produce a value. As we will see in the next sections, you can create algebraic operations on the right side of SELECT.

As mentioned already, unlike PRINT, SELECT can be used to display more than one value. The values must be separated by commas. Here is an example:

SELECT 'Hourly Salary', 24.85

Practical LearningPractical Learning: Selecting Something

  1. On the form, double-click the Operate button
  2. To use a SELECT statement, implement the Click event as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("SELECT 'William';",
    					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	txtFirstName.Text = rdr[0].ToString();
    
        rdr.Close();
        connection.Close();
    }
  3. Return to the form and double-click the Close button
  4. Implement its Click event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  5. Execute the application
  6. Click the Operate button
  7. After checking that a first name displays, close the form and return to your programming environment
  8. To select more than one value, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("SELECT 'William', 'Godetsky';",
    					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
    	txtFirstName.Text = rdr[0].ToString();
    	txtLastName.Text = rdr[1].ToString();
        }
    
        rdr.Close();
        connection.Close();
    }
  9. Execute the application
  10. Click the Operate button
     
    SELECTing more than one value
  11. Close the form and return to your programming environment

Nesting a SELECT Statement

Because we mentioned that the thing on the right side must produce a result, you can as well use another SELECT statement that it itself evaluates to a result. To distinguish the SELECT sections, the second one should be included in parentheses. Here is an example:

SELECT (SELECT 448.25);
GO

When one SELECT statement is created after another, the second is referred to as nested.

Just as you can nest one SELECT statement inside of another, you can also nest one statement in another statement that itself is nested. Here is an example:

SELECT (SELECT (SELECT 1350.75));
GO

SELECT This AS That

In the above introductions, we used either PRINT or SELECT to display something in the query window. One of the characteristics of SELECT is that it can segment its result in different sections. SELECT represents each value in a section called a column. Each column is represented with a name also called a caption. By default, the caption displays as "(No column name)". If you want to use your own caption, on the right side of an expression, type the AS keyword followed by the desired caption. The item on the right side of the AS keyword must be considered as one word. Here is an example:

SELECT 24.85 AS HourlySalary;

This would produce:

SELECT This AS That

You can also include the item on the right side of AS in single-quotes. Here is an example:

SELECT 24.85 AS 'HourlySalary';

If the item on the right side of AS is in different words, you should include it in single-quotes or put them in inside of an opening square bracket "[" and a closing square bracket "]". Here is an example:

SELECT 24.85 AS 'Hourly Salary';

If you create different sections, separated by a comma, you can follow each with AS and a caption. Here is an example:

SELECT 'James Knight' As FullName, 20.48 AS Salary;

This would produce:

SELECT this AS that

The above statement could also be written as follows:

SELECT 'James Knight' As [Full Name], 20.48 AS [Hourly Salary];

Math Operators

 

The Unary Positive and Negative Operators

A unary operator is an operator that performs its operation on only one operand.

As you may know from C#, to express a positive number, you can write + on its left side. As a mathematical convention, when a value is positive, you do not need to express it with the + operator.

The - sign must be typed on the left side of a number to make it negative.

Arithmetic Operators

To add two numbers, you use the addition operator. Here is an example:

PRINT 125 + 4088

In Transact-SQL, you can also perform the addition on text. Here is an example:

PRINT 'Henry ' + 'Kono'

To subtract two numbers, you use the - operator. Here is an example:

PRINT 1240 - 608

Unlike the addition, the subtraction operation is not associative. This means that a - b - c is not necessarily equal to c - b - a. This is illustrated in the following statements:

PRINT 128 - 42 - 5
PRINT 5 - 42 - 128

This would produce:

81
-165

Notice that both operations of the addition convey the same result.

To multiply two numbers, you use the * operator. Here is an example:

PRINT 128 * 42

This would produce 5376

To divide two numbers you use the / operator. Here is an example:

PRINT 128 / 42

This would produce 3

When performing the division, be aware of its many rules. Never divide by zero (0). Make sure that you know the relationship(s) between the numbers involved in the operation.

To get the remainder of a division operation between two numbers, you use the percent operator %. Here is an example:

PRINT 128 % 42

This would produce 2.

SQL Operators

 

Parentheses

Like most computer languages, Transact-SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, as we will learn soon, parentheses allow a function to delimit the list of its arguments. Parentheses can also be used to isolate an operation or an expression with regards to another operation or expression. For example, when studying the algebraic operations, we saw that the subtraction is not associative and can lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. Here is an example:

PRINT (154 - 12) + 8
PRINT 154 - (12 + 8)

This would produce:

150
134

As you can see, using the parentheses controls how the whole operation would proceed. This difference can be even more accentuated if your operation includes 3 or more operators and 4 or more operands. Here is another example of a nested SELECT statement that uses parentheses:

SELECT 
    (SELECT 448.25 * 3) + 
    (SELECT 82.28 - 36.04);
GO

Bit Manipulations

When you use a value in your database or application, the value must be stored somewhere in the computer memory using a certain amount of space. A value occupies space that resembles a group of small boxes. In our human understanding, it is not always easy to figure out how a letter such as as B is stored in 7 seven small boxes when we know that B is only one letter.

Bit manipulation or a bit related operation allows you to control how values are stored in bits. This is not an operation you will need to perform very often, especially not in the early stages of your database. Nevertheless, bit operations (and related overloaded operators) are present in all or most programming environments, so much that you should be aware of what they do or what they offer.

One of the operations you can perform on a bit consists of reversing its value. That is, if a bit holds a value of 1, you may want to change it to 0 and vice-versa. This operation can be taken care of by the bitwise NOT operator that is represented with the tilde symbol ~

The bitwise NOT is a unary operator that must be placed on the left side of its operand as in

~Value

Here is an example:

PRINT ~158

To perform this operation, the Transact-SQL interpreter considers each bit that is part of the operand and inverts the value of each bit from 1 to 0 or from 0 to 1 depending on the value the bit is holding. This operation can be resumed in the following table:

Bit ~Bit
1 0
0 1

Consider a number with a byte value such as 248. In our study of numeric systems, we define how to convert numbers from one system to another. Based on this, the binary value of decimal 248 is 1111 1000 (and its hexadecimal value is 0xF8). If you apply the bitwise NOT operator on it to reverse the values of its bits, you would get the following result:

 Value 1 1 1 1 1 0 0 0
~Value 0 0 0 0 0 1 1 1

The bitwise & is a binary operator that uses the following syntax

Operand1 & Operand2

This operator considers two values and compares the bit of each with the corresponding bit of the other value. If both corresponding bits are 1, the comparison produces 1. Otherwise, that is, if either bit is 0, the comparison produces 0. This comparison is resumed as follows:

Bit1 Bit2 Bit1 & Bit2
0 0 0
1 0 0
0 1 0
1 1 1

Imagine you have two byte values represented as 187 and 242. The binary value of decimal 187 is 1011 1011 (and its hexadecimal value is 0xBB). The binary value of decimal 242 is 1111 0010 (and its hexadecimal value is 0xF2). Let’s compare these two values bit by bit, using the bitwise AND operator:

  Binary Decimal
N1 1 0 1 1 1 0 1 1 187
N2 1 1 1 1 0 0 1 0 242
N1 & N2 1 0 1 1 0 0 1 0 178

Most of the times, you will want the interpreter to perform this operation and use the result in your program. This means that you can get the result of this operation and possibly display it to the user. The above operation can be performed by the following program:

PRINT 187 & 242

This would produce 178

You can perform another type of comparison on bits using the bitwise OR operator that is represented by |. Its syntax is:

Value1 | Value2

Once again, the interpreter compares the corresponding bits of each operand. If at least one of the equivalent bits is 1, the comparison produces 1. The comparison produces 0 only if both bits are 0. This operation is resumed as follows:

Bit1 Bit2 Bit1 | Bit2
0 0 0
1 0 1
0 1 1
1 1 1

Once again, let’s consider decimals 187 and 242. Their bitwise OR comparison would render the following result:

  Binary Decimal
N1 1 0 1 1 1 0 1 1 187
N2 1 1 1 1 0 0 1 0 242
N1 | N2 1 1 1 1 1 0 1 1 251

You can also let the compiler perform the operation and produce a result. Here is an example:

PRINT 187 | 242

This would produce 251

Bits Comparison: The Bitwise-Exclusive XOR Operator ^

Like the previous two operators, the bitwise-exclusive OR operator performs a bit comparison of two values. It syntax is:

Value1 ^ Value2

The compiler compares the bit of one value to the corresponding bit of the other value. If one of the bits is 0 and the other is 1, the comparison produces 1. In the other two cases, that is, if both bits have the same value, the comparison produces 0. This operation is resumed as follows:

Bit1 Bit2 Bit1 ^ Bit2
0 0 0
1 0 1
0 1 1
1 1 0

We will again consider decimals 187 and 242. Their bitwise-exclusive XOR comparison would render the following result:

  Binary Decimal
N1 1 0 1 1 1 0 1 1 187
N2 1 1 1 1 0 0 1 0 242
N1 ^ N2 0 1 0 0 1 0 0 1 73

If the interpreter performs this operation, it can produce a result as in the following example:

PRINT 187 ^ 242;

This would produce 73.

 

Variables Fundamentals

 

Introduction

In the previous sections, we used some values such as 242 or 'James Knight'. These are constant values because we certainly know them before using them and we did not change them in our statements. If you intend to use a certain category of value over and over again, you can declare a variable for it. Like C#, the SQL supports variables.

Declaring Variables

To declare a variable, use the DECLARE keyword using the following formula:

DECLARE Options

The DECLARE keyword lets the interpreter know that you are declaring a variable. The DECLARE keyword is followed by a name for the variable. In Transact-SQL, the name of a variable starts with the @ sign. The name of a variable allows you to identify the area of memory where the value of the variable is stored. While other languages like C/C++, Pascal, Java, C#, etc, impose strict rules to names, Transact-SQL is extremely flexible. A name can be made of digits only. Here is an example:

DECLARE @264

Such a name made of digits can create confusion with a normal number. A name can also be made of one or more words.

To avoid confusion, to name our variable, here are the rules we will follow:

To declare a variable, as we will see in the next sections, after giving a name to a variable, you must also specify the amount of memory that the variable would need. The amount of memory is also called a data type. Therefore, the declaration of a variable uses the following formula:

DECLARE @VariableName DataType;

You can also declare more than one variable. To do that, separate them with a comma. The formula would be:

DECLARE @Variable1 DataType1, @Variable2 DataType2, @Variable_n DataType_n;

Unlike many other languages like C/C++, C#, Java, or Pascal, if you declare many variables that use the same data type, the name of each variable must be followed by its own data type.

Initializing a Variable

After declaring a variable, the interpreter reserves a memory for it but the space does not necessarily hold a recognizable value. This means that, at this time, the variable is null. One way you can change this is to give a value to the variable. This is referred to as initializing the variable.

Remember that a variable's name starts with @ and whenever you need to refer to the variable, you must make sure you include the @ sign. To initialize a variable, in the necessary section, type the SELECT or the SET keyword followed by the name of the variable, followed by the assignment operator "=", followed by an appropriate value. The formula used is:

SELECT @VariableName = DesiredValue

or

SET @VariableName = DesiredValue

Once a variable has been initialized, you can make its value available or display it. This time, you can type the name of the variable to the right side of PRINT or SELECT.

Data Types

 

Introduction

After setting the name of a variable, you must specify the amount of memory that the variable will need to store its value. Since there are various kinds of information a database can deal with, the SQL provides a set of data types.

Boolean Variables

Like C#, the SQL supports Boolean values. To declare a Boolean variable, you use the BIT or bit keyword. Here is an example:

DECLARE @IsOrganDonor bit;

After declaring a Boolean variable, you can initialize it with 0 or another integral value. If the variable is initialized with 0, it receives the Boolean value of False. If it is initialized with any other number, it receives a True value. Here is an example of using a Boolean variable:

Declaring a Boolean Variable


Practical LearningPractical Learning: Using a Boolean Variable

  1. To declare and use a Boolean variable, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("DECLARE @IsMarried bit; " +
    					"SET @IsMarried = 1; " +
    				         "SELECT @IsMarried; ",
        					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	MessageBox.Show("Is Married? " + rdr[0].ToString());
    
        rdr.Close();
        connection.Close();
    }
  2. Execute the application and click the Operate button
  3. Close the form and return to your programming environment
 

Integer Variables

Like C#, the SQL supports integers. If a variable would hold natural numbers in the range of -2,147,483,648 to 2,147,483,647, you can declare it with the int keyword as data type. Here is an example:

The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

Remember that you can also declare and use more than one variable. Here is an example:

DECLARE @IsMarried bit, @EmplStatus int;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SELECT @IsMarried AS [Is Married?],
       @EmplStatus AS [Employment Status];
GO

If you want to use very small numbers such as student's ages, or the number of pages of a brochure or newspaper, apply the tinyint data type to such a field. A variable with the tinyint data type can hold positive numbers that range from 0 to 255. Here is an example:

The smallint data type follows the same rules and principles as the C#'s short data type to store numbers that range between -32,768 and 32,767. Here is an example:

The bigint data type follows the same rules and principles as the C#'s long data type and can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Here is an example:

The binary data type is used for a variable that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all values of the variable would have the exact same length (or quantity).

If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary type also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals.

Practical LearningPractical Learning: Using an Integer Variable

  1. To declare and use an integer variable, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("DECLARE @IsMarried bit; " +
    					"SET @IsMarried = 1; " +
    				"SELECT @IsMarried AS [Married?]; ",
        connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	MessageBox.Show("Is Married? " + rdr[0].ToString());
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @EmplStatus int; " +
    		  	        "SET @EmplStatus = 2; " +
      			        "SELECT @EmplStatus; ",
    			        connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	MessageBox.Show("Employment Status: " + rdr[0].ToString());
    	cbxEmploymentStatus.SelectedIndex = int.Parse(rdr[0].ToString());
        }
    
        rdr.Close();
        connection.Close();
    }
  2. Execute the application and click the Operate button
  3. Close the form and return to your programming environment

Decimal Variables

To support decimal numbers, the SQL provides various data types. To declare a variable that can hold decimal numbers, you can use the numeric or the decimal data types (either decimal or numeric would produce the same effect in SQL Server). Here is an example:

To declare a variable that would hold decimal numbers with single precision, you can use the float or the real data type. Here is an example:

A precision is the number of digits used to display a numeric value. For example, the number 42005 has a precision of 5, while 226 has a precision value of 3. If the data type is specified as an integer (the int and its variants) or a floating-point number (float and real), the precision is fixed by the database and you can just accept the value set by the Microsoft SQL Server interpreter. For a decimal number (decimal or numeric data types), Microsoft SQL Server allows you to specify the amount of precision you want. The value must be an integer between 1 and 38 (28 if you are using SQL Server 7).

The scale of a number if the number of digits on the right side of the period (or the character set as the separator for decimal numbers for your language, as specified in Control Panel). The scale is used only for numbers that have a decimal part, which includes currency (money and smallmoney) and decimals (numeric and decimal). If a variable is declared with the decimal or numeric data type, you can specify the amount of scale you want. The value must be an integer between 0 and 18. Here is an example:

Declaring decimal variables

Remember that you can declare an use various variables. Here is an example: 

DECLARE @IsMarried bit,
        @EmplStatus int,
        @WeeklyHours Decimal(6,2);
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SELECT @IsMarried AS [Is Married?],
       @EmplStatus AS [Employment Status],
       @WeeklyHours AS Hours;
GO

Practical LearningPractical Learning: Using a Decimal Variable

  1. To declare and use a decimal variable, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("DECLARE @EmplStatus int; " +
    					"SET @EmplStatus = 2; " +
    					"SELECT @EmplStatus; ",
    					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
    	cbxEmploymentStatus.SelectedIndex = int.Parse(rdr[0].ToString());
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " +
    			     "SET @WeeklyHours = 36.50; " +
    			     "SELECT @WeeklyHours; ",
    			     connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	MessageBox.Show("Weekly Hours: " + rdr[0].ToString());
    	txtWeeklyHours.Text = rdr[0].ToString();
        }
    
        rdr.Close();
        connection.Close();
    }
  2. Execute the application and click the Operate button
  3. Close the form and return to your programming environment

Currency Variables

If a variable would hold monetary values, you can declare it with the money data type. A variable with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807. Here is an example:

While the money data type can be used for a variable that would hold large quantities of currency values, the smallmoney data type can be applied for a variable whose value cannot be lower than -214,748.3648 nor higher than 214,748.3647.

The precision and scale of a money or smallmoney variable are fixed by Microsoft SQL Server. The scale is fixed to 4.

Once again, remember that you can declare and use various variables. Here is an example: 

DECLARE @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly;
GO

This would produce:

Declaring currency variables

 

Practical LearningPractical Learning: Using Money and Small Money

  1. To declare and use a currency variable, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("DECLARE @EmplStatus int; " +
    					"SET @EmplStatus = 2; " +
    					"SELECT @EmplStatus; ",
      					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
     	cbxEmploymentStatus.SelectedIndex = int.Parse(rdr[0].ToString());
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " +
    			     "SET @WeeklyHours = 36.50; " +
    			     "SELECT @WeeklyHours; ",
       			     connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	txtWeeklyHours.Text = rdr[0].ToString();
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @HourlySalary SmallMoney; " +
    			     "SET @HourlySalary = 15.72; " +
    			     "SELECT @HourlySalary; ",
    			     connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	MessageBox.Show("Hourly Salary: " + rdr[0].ToString());
    	txtHourlySalary.Text = rdr[0].ToString();
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2), " +
    			     "@HourlySalary SmallMoney, " +
    			     "@WeeklySalary SmallMoney; " +
    			     "SET @WeeklyHours = 36.50; " +
    			     "SET @HourlySalary = 15.72; " +
    			     "SET @WeeklySalary = " +
    			     "@WeeklyHours * @HourlySalary; " +
    			     "SELECT @WeeklySalary; ",
    			     connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	MessageBox.Show("Weekly Salary: " + rdr[0].ToString());
     	txtWeeklySalary.Text = rdr[0].ToString(); ;
        }
    
        rdr.Close();
       connection.Close();
    }
  2. Execute the application and click the Operate button
  3. Close the form and return to your programming environment

Date and Time Variables

A DATETIME data type is used for a column whose data would consist of date and/or time values, just like the DateTime structure of the .NET Framework. The entries must be valid date or time values but Microsoft SQL Server allows a lot of flexibility, even to display a date in a non-traditional format. The date value of a datetime field can be comprised between January 1st, 1753 and December 31, 9999.

To initialize a DATETIME variable, include its value between single-quote. If the value is a date, separate the components of the value with the symbol recognized in Control Panel as the Date Separator:

Here is an example:

If the value is a time period, still include it in single-quotes. Inside of the quotes, follows the rules and formats specified in the Control Panel:

Here is an example:

DECLARE @DateHired DateTime,
        @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney;
SET @DateHired = '12/05/1998';
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @DateHired AS [Date Hired],
       @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly;
GO

This would produce:

Declaring string variables

The smalldatetime data type is an alternative to datetime. It follows the same rules and principles as the datetime data type except that a date value must be comprised between January 1st, 1900 and June 6, 2079.

Practical LearningPractical Learning: Using Date and Time Variables

  1. To use a date value, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("DECLARE @EmplStatus int; " +
    					"SET @EmplStatus = 2; " +
    					"SELECT @EmplStatus; ",
      					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
     	cbxEmploymentStatus.SelectedIndex = int.Parse(rdr[0].ToString());
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @DateHired DateTime; " +
    			     "SET @DateHired = '12/02/1998'; " +
    			     "SELECT @DateHired;",
    			     connection);
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
    	MessageBox.Show("Date Hired: " + rdr[0].ToString());
    	dtpDateHired.Value = DateTime.Parse(rdr[0].ToString());
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " +
    			     "SET @WeeklyHours = 36.50; " +
    			     "SELECT @WeeklyHours; ",
       			     connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	txtWeeklyHours.Text = rdr[0].ToString();
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @HourlySalary SmallMoney; " +
    			     "SET @HourlySalary = 15.72; " +
    			     "SELECT @HourlySalary; ",
    			     connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	MessageBox.Show("Hourly Salary: " + rdr[0].ToString());
    	txtHourlySalary.Text = rdr[0].ToString();
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2), " +
    			     "@HourlySalary SmallMoney, " +
    			     "@WeeklySalary SmallMoney; " +
    			     "SET @WeeklyHours = 36.50; " +
    			     "SET @HourlySalary = 15.72; " +
    			     "SET @WeeklySalary = " +
    			     "@WeeklyHours * @HourlySalary; " +
    			     "SELECT @WeeklySalary; ",
    			     connection);
        rdr = command.ExecuteReader();
        while (rdr.Read())
        {
    	MessageBox.Show("Weekly Salary: " + rdr[0].ToString());
     	txtWeeklySalary.Text = rdr[0].ToString(); ;
        }
    
        rdr.Close();
       connection.Close();
    }
  2. Execute the application and click the Operate button
  3. Close the form and return to your programming environment

Character Variables

Like C#, Transact-SQL supports character variables. To declare such a variable, use the char data type. Here is an example:

DECLARE @Gender char;

By default, the char data type can be applied to a variable that would hold one character at a time. After declaring the variable, when initializing it, include its value in single-quotes. Here is an example:

1> DECLARE @Gender char;
2> SET @Gender = 'M';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected)

If you include more than one character in the single-quotes, only the first (most left) character would be stored in the variable. Here is an example:

1> DECLARE @Gender char;
2> SET @Gender = 'Male';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected)

If a variable will hold strings of different lengths, declare it with the varchar data type. The maximum length of text that a field of varchar type can hold is equivalent to 8 kilobytes.

In some circumstances, you will need to change or specify the number of characters used in a string variable. Although a First Name and a Book Title variables should use the varchar type, both variables would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both variables would use the same data type but different lengths.

To specify the maximum number of characters that can be stored in a string variable, on the right side of char or varchar, type an opening and a closing parentheses. Inside of the parentheses, type the desired number.

 Here are examples:

DECLARE @FirstName varchar(20),
        @LastName varchar(20),
        @FullName varchar(40),
        @DateHired DateTime,
        @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney;
SET @FirstName    = 'Samuel';
SET @LastName     = 'Weinberg';
SET @FullName     = @LastName + ', ' +@FirstName;
SET @DateHired    = '12/05/1998';
SET @IsMarried    = 1;
SET @EmplStatus   = 2;
SET @WeeklyHours  = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As [Full Name],
       @DateHired AS [Date Hired],
       @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly;
GO

This would produce: 

Initializing string variables

To initialize the variable, if you are using the Command Prompt (SQLCMD.EXE), include its value between double-quotes. Here is an example:

If you are using a query window, don't include the string value in double-quotes; otherwise, you would receive an error:

Initializing string variables

Therefore, if using the query window, include the string in single-quotes:

String Variables

The text data type can be used on a variable whose data would consist of ASCII characters. As opposed to a varchar type of field, a text type of field can hold text that is longer than 8 kilobytes.

The nchar, nvarchar, and ntext types follow the same rules as the char, varchar, and text respectively, except that they can be applied to variables that would hold international characters, that is, characters of languages other than US English. This is done following the rules of Unicode formats.

Practical LearningPractical Learning: Using String Variables

  1. To declare and use some string variables, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command =
    	new SqlCommand("DECLARE @FirstName varchar(20); " +
    		       "SET @FirstName = 'Samuel'; " +
    		       "SELECT @FirstName; ", connection);
    		       connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
     	txtFirstName.Text = rdr[0].ToString();
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @LastName varchar(20); " +
    			     "SET @LastName = 'Weinberg'; " +
    			     "SELECT @LastName; ", connection);
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
        	txtLastName.Text = rdr[0].ToString();
        rdr.Close();
    
        command = new SqlCommand("DECLARE @FirstName varchar(20), " +
    			     " @LastName varchar(20), " +
    			     " @FullName varchar(40); " +
    			     "SET @FirstName = 'Samuel'; " +
    			     "SET @LastName = 'Weinberg'; " +
    			     "SET @FullName = @LastName + " +
    			     "', ' +@FirstName; " +
    			     "SELECT @FullName; ",
    			     connection);
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
    	MessageBox.Show("Full Name: " + rdr[0].ToString());
    	txtFullName.Text = rdr[0].ToString();
        }
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @DateHired DateTime; " +
    			     "SET @DateHired = '12/02/1998'; " +
    			     "SELECT @DateHired;",
    			     connection);
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	dtpDateHired.Value = DateTime.Parse(rdr[0].ToString());
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @EmplStatus int; " +
    			     "SET @EmplStatus = 2; " +
    			     "SELECT @EmplStatus; ",
        connection);
    
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	cbxEmploymentStatus.SelectedIndex =
    		int.Parse(rdr[0].ToString());
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " +
    			     "SET @WeeklyHours = 36.50; " +
    			     "SELECT @WeeklyHours; ",
    			     connection);
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	txtWeeklyHours.Text = rdr[0].ToString();
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @HourlySalary SmallMoney; " +
    			     "SET @HourlySalary = 15.72; " +
    			     "SELECT @HourlySalary; ",
        connection);
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	txtHourlySalary.Text = rdr[0].ToString();
    
        rdr.Close();
    
        command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2), " +
    			     "@HourlySalary SmallMoney, " +
    			     "@WeeklySalary SmallMoney; " +
    			     "SET @WeeklyHours = 36.50; " +
    			     "SET @HourlySalary = 15.72; " +
    			     "SET @WeeklySalary = " +
    			     "@WeeklyHours * @HourlySalary; " +
    			     "SELECT @WeeklySalary; ",
    			     connection);
        rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	txtWeeklySalary.Text = rdr[0].ToString(); ;
    
        rdr.Close();
        connection.Close();
    }
  2. Execute the application and click the Operate button
  3. Close the form and return to your programming environment
  4. To declare and use all variables at the same time, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command =
    	 new SqlCommand("DECLARE @FirstName varchar(20), " +
    			"@LastName varchar(20), " +
    			"@FullName varchar(40), " +
    			"@DateHired DateTime, " +
    			"@EmplStatus int, " +
    			"@WeeklyHours Decimal(6,2), " +
    			"@HourlySalary SmallMoney, " +
    			"@WeeklySalary SmallMoney; " +
    			"SET @FirstName = 'Samuel'; " +
    			"SET @LastName = 'Weinberg'; " +
    			"SET @FullName = @LastName + " +
    			"', ' +@FirstName; " +
    			"SET @DateHired = '12/02/1998'; " +
    			"SET @EmplStatus = 2; " +
    			"SET @WeeklyHours = 36.50; " +
    			"SET @HourlySalary = 15.72; " +
    			"SET @WeeklySalary = " +
    			"@WeeklyHours * @HourlySalary; " +
    			"SELECT @FirstName, @LastName, @FullName, " +
    			"@DateHired, @EmplStatus, @WeeklyHours, " +
    			"@HourlySalary, @WeeklySalary; ",
    			connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
    	txtFirstName.Text = rdr[0].ToString();
    	txtLastName.Text = rdr[1].ToString();
    	txtFullName.Text = rdr[2].ToString();
    	dtpDateHired.Value = DateTime.Parse(rdr[3].ToString());
    	cbxEmploymentStatus.SelectedIndex = int.Parse(rdr[4].ToString());
    	txtWeeklyHours.Text = string.Format("{0:F}", rdr[5]);
    	txtHourlySalary.Text = string.Format("{0:C}", rdr[6]);
    	txtWeeklySalary.Text = string.Format("{0:C}", rdr[7]);
        }
    
        rdr.Close();
        connection.Close();
    }
  5. Execute the application and click the Operate button
     
    Using Variables
  6. Close the form and return to your programming environment
  7. Change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        string strConnection =
    	"Data Source=(local);Integrated Security=yes";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
    	SqlCommand command =
    	    new SqlCommand("DROP DATABASE [Exercise4];",
    			   connection);
    
    	connection.Open();
    
    	command.ExecuteNonQuery();
    
    	MessageBox.Show(
    	    "The Exercise4 database has been deleted from the server.");
        }
    }
  8. Execute the application and click the Operate button
     
    Deleting a Database
  9. Close the form and return to your programming environment

Lesson Summary

 

Exercises

  1. Write a statement that, when given the yearly salary of a person, can evaluate the hourly salary (consider that the yearly salary is based on 40 hours a week)
  2. Write a statement so that, given the base and the height of a triangle, it calculates and displays the area (the area of a triangle is b * h / 2)

Previous Copyright © 2008-2009 Yevol.com Next