Introduction

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:

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

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.

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

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:

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

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

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:

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

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:

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.

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

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: 

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:

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

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.

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
    
   
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
    
   
9. Close the form and return to your programming environment