Transact-SQL is Microsoft’s and Sybase’s proprietary extension to the conventional SQL (structured query language). Transact-SQL is sometimes abbreviated as T-SQL. This enhancement brought procedural programming language functionalities, such as local variables, control-of-flow constructs, and additional functions for various needs.
The purpose of this two-part article is to give a brief overview on the usage of T-SQL. If you are already familiar with any SQL flavor, whether it's MySQL, Oracle, or perhaps SQL-92, that’s a huge benefit. However, throughout this article I will assume that you have at least some knowledge regarding relational databases.
We’re going to cover the following programming concepts:
Usage of variables (data types, etc.)
Conditional statements (IF…ELSE, BEGIN…END)
Usage of CASE command
Usage of WHILE loops
CONTINUE command
BREAK command
GOTO command
RETURN command
WAITFOR command
Usage of cursors
Usage of functions
Creating functions of your own
Now that I’ve given you a sense of what to expect, let’s begin!
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.
As I mentioned at the beginning of my article, one of the most outstanding enhancements that T-SQL brought to the traditional de facto standard SQL was the addition of the control-of-flow mechanism. Here we can enroll commands such as IF…END, BEGIN…END, BREAK, GOTO, CONTINUE, RETURN, WAITFOR, and WHILE.
The way we use conditional statements hasn’t changed. However, thanks to the T-SQL enhancement we can add a block of code into IF statements. This also means that we can nest more than one IF statement. An IF conditional works based on the following methodology: it executes the followed statements if and only if the testing is true. When the condition isn’t met, the ELSE part is executed (if there is one).
For each time you want to add a block of code, don’t forget to delimit these using the BEGIN and END commands. BEGIN…END groups the commands in between. Check out the following example.
IF condition1
BEGIN
lots of commands
END
ELSE
IF condition2
BEGIN
another pack of commands
END
ELSE
BEGIN
yet another batch of commands
END
I hope that the above example is intuitive. Here it is in plain English: if condition1 is met, then the lots of commands part is executed; if condition1 isn’t met, then the second condition2 is tested; if it’s validated as true, then another pack of commands is executed; however, if this also returns a false validation, then, obviously, yetanother batch of commands is executed, since there aren’t any more conditions.
Makes sense, doesn’t it? Great! Now let’s see how the CASE commands act.
CASE is a command that handles a list of conditionals and returns the execution of one of them. There are two formats in which you can use the CASE command. The first one is the simple CASE that works on the basis of comparison. Once the condition is met, the following expressions are returned. The second type is the searched CASE that evaluates a list of Boolean expressions in order to determine the results.
Check out the following “simple CASE” example. Can you guess what it does?
DECLARE @stateshort nchar(2)
DECLARE @statelong nchar(8)
SET @stateshort = ’DS’
SELECT @statelong =
CASE @stateshort
WHEN ’SC’ THEN ’SEO Chat’
WHEN ’DS’ THEN ’Dev Shed’
WHEN ’AF’ THEN ’SP Free’
END
Yes, that’s right! Indeed, the content of the stateshort variable is evaluated, which is "DS," and it is met on the second conditional line. As a result the statelong variable ends up receiving the "Dev Shed" value. Notice that we’ve set the variable statelong after SELECT, so we can “work with it” once one of the conditions are met.
Loops are a critical part of programming languages. We use loops each time a specific segment or block of code needs to be executed multiple times. In this case, the WHILE loop executes the block of code until the condition is met (i.e. until the conditional expression returns true). The syntax is pretty straightforward: WHILE <statement> followed by the block of code delimited by a BEGIN…END structure.
DECLARE @MyNumber int
SET @MyNumber = 10
WHILE(@MyNumber>0)
BEGIN
PRINT ’MyNumber = ’ + CONVERT(nvarchar, @count)
SET @MyNumber = @MyNumber -1
END
The above example prints out the following sequence: “MyNumber = 10,” “MyNumber = 9,” “MyNumber = 8” … “MyNumber = 2,” and "MyNumber = 1.” We use MyNumber as an index for the WHILE loop. Its integer value gets decremented after each loop. Therefore, the loop works as long as it’s a larger number than zero.
We have a branch of other commands that we can use inside a WHILE loop. One of those powerful commands is CONTINUE. Simply put, it ignores the block of code (statements, etc.) that follows this "continue." It restarts the loop, moving to the next stage.
Another instruction that we shouldn’t forget about is the BREAK. This command exits the innermost WHILE loop. It skips the block of code and jumps directly to the END, which marks the termination of the loop. It’s really important that if more WHILE loops are nested, then BREAK exits the outermost loop, executing the statements that are after the inner loop’s end. And once they’re done, it restarts the outermost loop.
The RETURN instruction ends the execution without relying on any conditions, ignoring all of the statements following this keyword. You may add an integer at the end of this command if you want to pass on. The main thing you need to know is that RETURN is immediate and exits subroutines (stored procedures) and functions.
Usually if you do not specify a return value, then MS SQL Server returns one of the reserved values (-1 and -14). In general, 0 is considered successful completion of the stored procedure. So if you want to point out that a particular procedure succeeded, then type “RETURN 0” and be done with it. It’s more professional, safer, and clearer.
GOTO is an old-school code jump statement that does not get much love from experts, especially in high level programming languages. But it’s present in T-SQL, so let’s see how to use it. It works based on the following methodology: it defines a label and allows the programmer to instruct its code to jump to that label and continue with the execution. The label name is built by an identifier followed by a colon.
However, we need a little clarification: GOTO statements can be nested, but they can only be used within a statement block, batch, or stored procedure. This means that you cannot instruct a GOTO to jump to a label that’s located outside of the said batch.
A classic example of GOTO is used to replace a WHILE loop. Check it out!
DECLARE @count tinyint
SET @count = 0
MyLabel:
PRINT 'Developer Shed'
SET @count = @count + 1
IF @count <=4
GOTO MyLabel
The above code prints out "Developer Shed" 4 times. MyLabel is the label.
The last instruction that we’re going to cover in this article is WAITFOR. With this command we can instruct the program to delay the execution of instructions for a time period that we explicitly specify. Simply put, WAITFOR pauses the flow of execution until the specific time or time interval is reached.
There are two types of WAITFOR formats. We use the first if we want to specify the amount of time that the execution is going to be delayed. The syntax for this is: WAITFOR DELAY '00:00:10' --this results in a delay that’s 10 seconds long. However, should we need to specify an exact time, we use: WAITFOR TIME '05:00:00' - this results in the following segment executing at 5:00AM sharp.
Taking a Break
We’ve come to the end of this half. I truly hope that you found this article informative. T-SQL is by no means a hard language. So it’s more than enough to point out the specific functions, commands, instructions, etc., to explain what each of them does and how to implement them according to your needs. After you’ve developed familiarity, then you need to practice. Don’t forget that practice makes perfect.
We don’t have time to waste. In the next segment we are going to cover cursor-related programming techniques and instructions. We are going to learn how to use the FETCH command and how to implement proprietary variables such as @@FETCH_STATUS. Don’t be afraid. Like I said, T-SQL is a language that makes sense.
Furthermore we will spend our time delving into the world of functions. You can anticipate a thorough overview on the function categories. At first, we explore the vast amount of built-in functions and then move on to design the functions on our own.
MS SQL Server 
/ 9 
Completing the Introduction to Transact-SQL
