A Brief Introduction to Transact-SQL - Usage of Variables
(Page 2 of 4 )
Usage of Variables
All of the data types and their descriptions are summed up in the table below.
Data Type | Description of the Data Type |
bit | Integer data with either a 1 or 0 value. |
int | Integer data from -2^31 through 2^31–1. |
smallint | Integer data from 2^15 through 2^15–1. |
bigint | Integer data from -2^63 through 2^63–1. |
tinyint | Integer data from 0 through 255. |
decimal | Fixed precision and scale numeric data from -10^38-1 through 10^38-1. |
numeric | Same as decimal (synonym); see above. |
money | Monetary data values from -2^63 through 2^63-1, with accuracy to a ten-thousandth of a monetary unit. |
smallmoney | Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. |
float | Floating precision number data from -1.79E+308 through 1.79E+308. |
real | Floating precision number data from -3.40E+38 through 3.40E+38. |
datetime | Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. |
smalldatetime | Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute. |
cursor | A reference to a cursor. |
timestamp | A database-wide unique number. |
uniqueidentifier | A globally unique identifier (GUID). |
char | Non-Unicode character data with a maximum length of 8,000 characters. |
varchar | Non-Unicode data with a maximum of 8,000 characters. |
text | Non-Unicode data with a maximum length of 2^31-1 characters. |
nchar | Unicode data with a maximum length of 4,000 characters. |
nvarchar | Unicode data with a maximum length of 4,000 characters. |
ntext | Unicode data with a maximum length of 2^31-1 characters. |
binary | Fixed length binary data with a maximum length of 8,000 bytes. |
varbinary | Binary data with a maximum length of 8,000 bytes. |
image | Binary data with a maximum length of 2^31–1 bytes. |
xml | Variable for storing XML data (column instances or xml type). |
sql_variant | Various SQL Server data types excluding text, ntext, image, time_stamp, sql_variant. |
table | Storage for a set of rows. |
Now the time has come for us to learn how to declare variables. We can declare variables using the DECLARE command followed by the name of the variable and its type. It’s really important to remember to write the so-called @ (at sign) as a prefix for the variable name. Check out the following example.
DECLARE @MyVariable nvarchar(20)
You can declare more than one variable in a single line. See the next example.
DECLARE @MyVariable nvarchar(20), @MyInt int, @MyInt2 int
After declaration, the variables get a null value. However, you can manually set a specific value by using the SET command, followed by the variable and the value. Here’s a real-world example:
SET @MyVariable = ’string’
SET @MyInt = 1
Furthermore, you can create user data types. You can do this by using the command sp_addtype followed by three parameters: the name of the data type that’s being created, the adaptive-server supplied data type on which it’s being formed, and either null, not null, or identity. The last parameter is optional.
Additional notes regarding creating your own data types: if you don’t specify the precision for float in parentheses then the platform’s default is used; data types, such as char, nchar, nvarchar, and binary, all expect a particular length in parentheses. If you don’t specify this length, then 1 is going to be chosen by default. Be aware of these!
sp_addtype LicensePlate, "char(7)", "not null"
When you want to drop some of the data types you defined, you can use the command sp_droptype, followed by the name of the data type. Check out the example below to see how to do this. However, don’t forget that you cannot drop a type that’s currently in use.
sp_droptype LicensePlate
That’s all for declaring variables and it’s rather easy. Let’s move on to conditionals.
Next: Control of Flow Mechanisms >>
More MS SQL Server Articles
More By Barzan "Tony" Antal
/ 6 
