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 cannot be one of the following words reserved for Transact-SQL internal keywords:
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. 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. 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: 
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
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 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;
}
}
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. 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.
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);
}
}
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);
}
}
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 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
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.
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:
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 ".".
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:
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:
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:
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:
Once again, it is better to provide a year with 4 digits.
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:
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.
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) 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.
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. 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);
}
}
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.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Practical
Learning: Introducing Variables
