Home

Transact-SQL Data Types and Variables

   

Variables Fundamentals

 

Introduction

Like C#, Transact-SQL supports the ability to declare an use variables. These are values that are stored in the computer memory.

Practical LearningPractical Learning: Introducing Variables

  1. Start Microsoft Visual Studio
  2. Create a Windows Forms Application named Exercise23
  3. From the Common Controls section of the Toolbox, add a Button to the form
  4. 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."),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information;
        }
    }
  5. Execute the application
  6. Click the button
  7. Click OK
  8. Close the form and return to your programming environment
  9. 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  
  10. On the form, double-click the Operate button
  11. 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();
    }
  12. Return to the form and double-click the Close button
  13. Implement its Click event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  14. Execute the application
  15. Click the Operate button
  16. After checking that a first name displays, close the form and return to your programming environment
  17. 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();
    }
  18. Execute the application
  19. Click the Operate button
     
    SELECTing more than one value
  20. Close the form and return to your programming environment

Declaring a Variable

To declare a variable, use the DECLARE keyword in 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

There are rules and suggestions you will use for the names:

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

A name cannot be one of the following words reserved for Transact-SQL internal keywords:

aggregate alter and any application
as assembly backup begin between
bigint binary bit break broker
by case catalog catch certificate
char check checkpoint close commit
compute contains continue contract create
credential cursor database date datetime
datetime2 datetimeoffset deallocate decimal declare
default delete deny disable drop
else enable end endpoint event
exec execute false fetch float
foreign from full fulltext function
go goto grant group having
hierarchyid if image in index
insert int into is kill
like login master merge message
money move nchar next not
ntext null numeric nvarchar on
order output partition print proc
procedure queue real receive remote
resource return revert revoke role
rollback rowversion rule save schema
select send set setuser shutdown
smalldatetime smallint smallmoney sql_variant status
table text then time timestamp
tinyint tran transaction trigger true
try type union unique uniqueidentifier
update use values varbinary varchar
view when while with xml

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;

The name of each variable must be followed by its own data type.

Initializing a Variable

After declaring a variable, to initialize it, 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 use its value.

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

Natural Numbers

 

Regular Integers

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

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

Tiny Integers

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:

tinyint

Small Integers

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 that uses a smallint:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @NumberOfPages smallint; " +
                                                "SET @NumberOfPages = 748; " +
                                                "SELECT @NumberOfPages; ",
                                                connection);
            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Number of Pages: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Long Integers

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.

Binary Integers

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.

Decimal Numbers

 

Introduction

Like C#, Transact-SQL suports decimal number with single or double-precistion. If you want to use a variable whose values will be decimal, specify its data type as numeric or decimal. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @Distance decimal; " +
                                            "SET @Distance = 648.16; " +
                                            "SELECT @Distance; ",
                                            connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
            MessageBox.Show("Distance: " + rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
    }
}

Real Numeric Types

Transact-SQL supports decimal numbers whose precision is not particularly important. To declare such a variable, use the float or the real keyword. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
     using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @Radius float; " +
                                            "SET     @Radius = 52.49; " +
                                            "SELECT  @Radius; ",
                                            connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while( rdr.Read() )
                MessageBox.Show("Radius: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
    }
}

The Precision of a Decimal Number

A precision is the number of digits used to represent 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.

The Scale of a Decimal Number

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: 

DECLARE @WeeklyTime Decimal(6,2);SET     @WeeklyTime = 36.50;
SELECT  @WeeklyTime AS [Time Worked];
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 Values

 

Money

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:

Small Money

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.

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 Types

 

Time Values

Like C# (or rather the .NET Framework), Transact-SQL supports time values. To support time values, Transact-SQL provides the TIME data type. To declare a variable that would hold a time value, use TIME as the data type. The primary formulas of a time values are defined in the Time tab of the Customize Regional Options of Control Panel:

To initialize the variable, use the following formula:

hh:mm
hh:mm:ss
hh:mm:ss[.fractional seconds]

The first part includes the hour with a value between 1 and 23. If the value is less than 10, you can write it with a leading 0, as in 08.

The second part represents the minutes and holds a value between 1 and 59. If the value is less than 10, you can type it with a leading 0, as in 04. The values are separated by :. The value is included in single-quotes. To indicate that you want to follow Unicode rules, precede the value with N. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @ArrivalTime time; " +
                                            "SET     @ArrivalTime = N'18:22'; " +
                                            "SELECT  @ArrivalTime; ",
                                            connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
            MessageBox.Show("Arrival Time: " + rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
    }
}

This would produce:

Time Value

The third part of our formula is optional and represents the seconds portion of the time and holds a value between 1 and 59. If the value is less than 10, you can provide it with a leading 0. This part is separated from the previous one with :.

The last part also is optional. It allows you to provide the milliseconds part of the time. If you want to provide it, enter a value between 1 and 999. This is separated from the seconds part with a period ".". 

Date Values

To assist you with date values, Transact-SQL provides the DATE data type. This data type counts dates starting from January 1st, 0001 up to December 31st, 9999. Therefore, to declare a variable that would hold a date value, use the DATE data type.

The primary rules of date values are defined in the Date tab of the Customize Regional Options accessible from Control Panel:

Here is an example:

To initialize a DATE variable, use one of the following formulas:

YYYYMMDD
YYYY-MM-DD
MM-DD-YY
MM-DD-YYYY
MM/DD/YY
MM/DD/YYYY

You can start the value with a 4-year digit. If you use the first formula, YYYYMMDD, you must provide 4 digits for the year, immediately followed by 2 digits for the month, immediately followed by 2 digits for the day. An example would be:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @OneDay DATE; " +
                                            "SET     @OneDay = N'10360610'; " +
                                            "SELECT  @OneDay; ",
                                            connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
            MessageBox.Show("Day to Prevail: " + rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
    }
}

This would produce:

Time Value

In US English, this represents October 6th, 1036. You can provide the value in one unit with 6 digits. In this case, the left 2 digits would be considered the year in the current century. Here is an example:

DECLARE @OneDay DATE;
SET @OneDay = N'360610';
SELECT @OneDay AS [Day to Prevail];
GO

Instead of providing the whole value in one combination of digits, you can use the second formula. Once again you must provide 4 digits for the year, followed by the "-" separator, followed by 1 or 2 digits for the month, followed by the "-" separator, followed by 1 or 2 digits for the day. An example would be

DECLARE @EventDay date;
SET @EventDay = N'1914-4-7';
SELECT @EventDay AS [Event Day];
GO

In US English, this represents October 6th, 1036.

If you are using a command prompt or PowerShell, make sure you include the value in single-quotes. To apply Unicode rules, start with the N prefix. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @IndependenceDay date; " +
                                      "SET     @IndependenceDay = N'07/04/1776'; " +
                                            "SELECT  @IndependenceDay; ",
                                            connection);

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

        while (rdr.Read())
            MessageBox.Show("Independence Day: " + rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
    }
}

This would produce:

Date Value

We saw that, if you use the MM-DD-YY or MM/DD/YY, you can provide a year with 2 digits. In this case:

  1. If the number representing the year is less than 50, the year would be considered as belonging to the current century
  2. If the number representing the year is greater than 50, the year is considered as belonging to the previous century

Here are examples:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                                "SET     @SomeDate = N'5-7-05'; " +
                                                "SELECT  @SomeDate; ",
                                                connection);

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

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5/7/05'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

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

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5-7-41'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

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

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5/7/41'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

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

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5-7-81'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

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

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5/7/81'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

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

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

Date Value

Date Value

Date Value

Once again, it is better to provide a year with 4 digits.

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 DATE; " +
    			     "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

Combining Date and Time Values

Instead of singly declaring a date or a time value, you may want to combine both values into one. To support this, Transact-SQL provides the DATETIME2 data type. This data type counts dates from January 1st, 0001 and ends on December 31st, 9999. Therefore, to declare a variable that supports a date value, a time value, or a combination of a date and time values, use the DATETIME2 data type. To initialize the variable, use one of the following formulas:

YYYYMMDD
YYYYMMDD hh:mm:ss
YYYYMMDD hh:mm:ss[.fractional seconds]
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
YYYY-MM-DD hh:mm:ss[.fractional seconds]
MM-DD-YY
MM-DD-YY hh:mm:ss
MM-DD-YY hh:mm:ss[.fractional seconds]
MM-DD-YYYY
MM-DD-YYYY hh:mm:ss
MM-DD-YYYY hh:mm:ss[.fractional seconds]
MM/DD/YY
MM/DD/YY hh:mm:ss
MM/DD/YY hh:mm:ss[.fractional seconds]
MM/DD/YYYY
MM/DD/YYYY hh:mm:ss
MM/DD/YYYY hh:mm:ss[.fractional seconds]

Remember to include the value in single-quotes. Here are examples:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        string result = "";

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @FullName nvarchar(60), " +
                                          "        @DateOfBirth date, " +
                                          "        @DateRegistered datetime2; " +
                                          "SET @FullName       = N'John Summons'; " +
                                          "SET @DateOfBirth    = N'19960426'; " +
                                          "SET @DateRegistered = N'20100629'; " +
                                          "SELECT @FullName, " +
                                          "       @DateOfBirth, " +
                                          "       @DateRegistered; ",
                                          connection);

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

            while (rdr.Read()){
                result = string.Format("Full Name:\t{0}\n" +
                                       "Date of Birth:\t{1}\n" +
                                       "Date Registered:\t{2}",
                                       rdr[0].ToString(),
                                       rdr[1].ToString(),
                                       rdr[2].ToString());
            }
             
            MessageBox.Show(result,
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @FullName nvarchar(60), " +
                                           "        @DateOfBirth date, " +
                                           "        @DateRegistered datetime2; " +
                                           "SET @FullName       = N'James Haans'; " +
                                           "SET @DateOfBirth    = N'1994-10-25'; " +
                                           "SET @DateRegistered = N'2009-08-02'; " +
                                           "SELECT @FullName, " +
                                           "       @DateOfBirth, " +
                                           "       @DateRegistered; ",
                                           connection);

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

            while (rdr.Read())
            {
                result = string.Format("Full Name:\t{0}\n" +
                                       "Date of Birth:\t{1}\n" +
                                       "Date Registered:\t{2}",
                                       rdr[0].ToString(),
                                       rdr[1].ToString(),
                                       rdr[2].ToString());
            }

            MessageBox.Show(result,
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @FullName nvarchar(60), " +
                                      "        @DateOfBirth date, " +
                                      "        @DateRegistered datetime2; " +
                                      "SET @FullName       = N'Gertrude Monay'; " +
                                      "SET @DateOfBirth    = N'06-16-92'; " +
                                      "SET @DateRegistered = N'2009-12-24 12:36'; " +
                                      "SELECT @FullName, " +
                                      "       @DateOfBirth, " +
                                      "       @DateRegistered; ",
                                      connection);

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

            while (rdr.Read())
            {
                result = string.Format("Full Name:\t{0}\n" +
                                       "Date of Birth:\t{1}\n" +
                                       "Date Registered:\t{2}",
                                       rdr[0].ToString(),
                                       rdr[1].ToString(),
                                       rdr[2].ToString());
            }

            MessageBox.Show(result,
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @FullName nvarchar(60), " +
                                 "        @DateOfBirth date, " +
                                 "        @DateRegistered datetime2; " +
                                 "SET @FullName       = N'Philomène Guillon'; " +
                                 "SET @DateOfBirth    = N'1996-10-16'; " +
                                 "SET @DateRegistered = N'10/14/08 09:42:05.136'; " +
                                 "SELECT @FullName, " +
                                 "       @DateOfBirth, " +
                                 "       @DateRegistered; ",
                                 connection);

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

            while (rdr.Read())
            {
                result = string.Format("Full Name:\t{0}\n" +
                                       "Date of Birth:\t{1}\n" +
                                       "Date Registered:\t{2}",
                                       rdr[0].ToString(),
                                       rdr[1].ToString(),
                                       rdr[2].ToString());
            }

            MessageBox.Show(result,
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @FullName nvarchar(60), " +
                                      "        @DateOfBirth date, " +
                                      "        @DateRegistered datetime2; " +
                                      "SET @FullName       = N'Eddie Monsoon'; " +
                                      "SET @DateOfBirth    = N'08/10/96'; " +
                                      "SET @DateRegistered = N'2009-06-02 12:36'; " +
                                      "SELECT @FullName, " +
                                      "       @DateOfBirth, " +
                                      "       @DateRegistered; ",
                                      connection);

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

            while (rdr.Read())
            {
                result = string.Format("Full Name:\t{0}\n" +
                                       "Date of Birth:\t{1}\n" +
                                       "Date Registered:\t{2}",
                                       rdr[0].ToString(),
                                       rdr[1].ToString(),
                                       rdr[2].ToString());
            }

            MessageBox.Show(result,
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @FullName nvarchar(60), " +
                                "        @DateOfBirth date, " +
                                "        @DateRegistered datetime2; " +
                                "SET @FullName       = N'Peter Mukoko'; " +
                                "SET @DateOfBirth    = N'03-10-1994'; " +
                                "SET @DateRegistered = N'7/22/2009 10:24:46.248'; " +
                                "SELECT @FullName, " +
                                "       @DateOfBirth, " +
                                "       @DateRegistered; ",
                                connection);

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

            while (rdr.Read())
            {
                result = string.Format("Full Name:\t{0}\n" +
                                       "Date of Birth:\t{1}\n" +
                                       "Date Registered:\t{2}",
                                       rdr[0].ToString(),
                                       rdr[1].ToString(),
                                       rdr[2].ToString());
            }

            MessageBox.Show(result,
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @FullName nvarchar(60), " +
                                      "        @DateOfBirth date, " +
                                      "        @DateRegistered datetime2; " +
                                      "SET @FullName       = N'Chritian Allen'; " +
                                      "SET @DateOfBirth    = N'06/16/1995'; " +
                                      "SET @DateRegistered = N'02-09-2009 12:36'; " +
                                      "SELECT @FullName, " +
                                      "       @DateOfBirth, " +
                                      "       @DateRegistered; ",
                                      connection);

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

            while (rdr.Read())
            {
                result = string.Format("Full Name:\t{0}\n" +
                                       "Date of Birth:\t{1}\n" +
                                       "Date Registered:\t{2}",
                                       rdr[0].ToString(),
                                       rdr[1].ToString(),
                                       rdr[2].ToString());
            }

            MessageBox.Show(result,
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

Date Date
Date Date
Date Date
Date

If you start the value with two digits, the first part is considered a month and not the year.

Besides the DATE, the TIME, and the DATETIME2 data types, Transact-SQL supports the smalldatetime and the datetime data types. These are old data types. Although still available, they are kept for backward compatibility and you should stop using them.

Characters and Strings

 

Character Values

A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a variable to hold a fixed number of characters, such as the book shelf numbers of a library, declare it with 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)

By default, when you initialize a character variable, the interpreter reserves 8 bits of memory for the variable. This could be a problem if you want to store characters other than those used in US English. The alternative is to ask the interpreter to reserve 16 bits of space and follow Unicode rules. To do this, when initializing the variable, precede its value with N. Here is an example:

1> DECLARE @Gender char;
2> SET @GENDER = N'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 = N'Male';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected) 

Strings

A string is a character or a combination of characters. 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. To initialize the variable, if you are using the Command Prompt (SQLCMD.EXE), include its value between double-quotes. Here is an example:

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.

When initializing the variable, to follow Unicode rules, precede its value with N follow by single-quotes. This rule applies to both the Query window and PowerShell:

1> DECLARE @FirstName    nchar(20),
2>         @LastName     nchar(20);
3> SET     @FirstName =  N'Philomène';
4> SET     @LastName  =  N'Açore';
5> SELECT  @FirstName As "Prénom",
6>         @LastName  As "Nom de Famille";
7> GO
Prénom               Nom de Famille
-------------------- --------------------
Philomène            Açore

(1 rows affected)
1>

In Powershell, if you are not using Unicode rules, the string must be included in double-quotes and if you are using Unicode, the string must be included in single-quotes.

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

User-Defined Types

 

Introduction

If you have programmed in languages like C/C++, Pascal, or ADA, etc, you are probably familiar with the ability to give a friendly name to a known data type. Transact-SQL also gives you this option. A user-defined data type (UDT) is a technique of creating a data type based on an existing Transact-SQL data type.

Creating a User-Defined Type

Before creating a user-defined data type, you must be familiar with the existing types. If you want, you can create an alias name for one of these. To create a UDT in Transact-SQL, the basic formula to follow is:

CREATE TYPE AliasName FROM BaseType

You start with the CREATE TYPE expression, followed by the desired name for the new type. After the FROM keyword, type an existing Transact-SQL data type. Here is an example:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
          SqlCommand command = new SqlCommand("CREATE TYPE NaturalNumber FROM int; ",
                                                connection);

            connection.Open();
            command.ExecuteNonQuery();
            
            MessageBox.Show("A user-defined data type has been created",
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

In the same way, you can create as many aliases of known data types as you want. You must also be aware of rules that govern each data type. Here are examples:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
   {
 SqlCommand command = new SqlCommand("CREATE TYPE ShortString FROM nvarchar(20); " +
                                           "CREATE TYPE ItemCode FROM nchar(10); " +
                                     "CREATE TYPE LongString FROM nvarchar(80); " +
                                     "CREATE TYPE Salary FROM decimal(8, 2); " +
                                            "CREATE TYPE Boolean FROM bit;",
                                            connection);

        connection.Open();
        command.ExecuteNonQuery();
            
        MessageBox.Show("User-defined types have been created",
                        "Database Application",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

Using a User-Defined Type

After creating a UDT, you can use it as you see fit. For example, you can declare a variable for it. Then, before using it, you must initialize it with the appropriate value. Here are examples:

DECLARE @EmployeeID NaturalNumber,
	@EmployeeNumber ItemCode,
        @FirstName ShortString,
        @LastName ShortString,
        @Address LongString,
        @HourlySalary Salary,
        @IsMarried Boolean;
SET     @EmployeeID = 1;
SET	@EmployeeNumber = N'28-380';
SET     @FirstName = N'Gertrude';
SET     @LastName = N'Monay';
SET     @Address = N'1044 Alicot Drive';
SET     @HourlySalary = 26.75;
SET     @IsMarried = 1;
SELECT  @EmployeeID AS [Empl ID],  @EmployeeNumber AS [Empl #],
        @FirstName AS [First Name], @LastName AS [Last Name],
        @Address, @HourlySalary AS [Hourly Salary],
        @IsMarried AS [Is Married ?];
GO

You can mix Transact-SQL data types and your own defined type in your code.

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 © 2010-2011 C#Key Next