Overview

• T-SQL starts with a SQL foundation in which traditional SQL commands, such as SELECT, INSERT, UPDATE, DELETE, CREATE and DROP, work alongside proprietary commands and processes, called extensions, that change how you program and manipulate the database as well as expand its functionality. T-SQL extensions include stored procedures, triggers and transaction control methods, all of which alter programming functions such as error handling, declaring variables, and using conditional and row processing procedures.
  
  

Stored Procedures

• Stored procedures and the functionality they provide are a T-SQL specialty. Each is a collection of SQL instructions, flow-control statements and, often, parameters that guide program execution. Use built-in or system stored procedures -- identified by the tag “sp_procedure name” -- for common tasks such as adding users, accessing help files, performing database lock actions, identifying database activity levels and executing command-line operations. Write your own using the CREATE PROCEDURE command, save and then reuse custom stored procedures to access specific information.
  
  

Triggers

• T-SQL trigger are special instructions, in the form of stored procedures, that police the database and play a role in data security. Because their main function is to protect data, triggers are most often associated with the traditional SQL commands INSERT, DELETE and/or UPDATE. For example, when a user tries to delete information from the database, a behind-the-scenes trigger can fire and prevent the DELETE action from occurring. The parameters you include in a trigger command determine the length of the code, but the basic syntax for a trigger includes the CREATE TRIGGER command, the name of the trigger, the table or view on which you want it to work, and the trigger parameters.
  
  

Manipulating Data

• Cursors increase control over how you work with and manipulate data. Instead of working in a row-by-row fashion, cursors allow you to process whole sets of data, or multiple rows, at a time. Each cursor includes a declaration section that creates and opens the cursor and identifies its parameters, a fetch section that allows it to grab the data you need, and a close section to dissolve the cursor and end its operations.
  
  

Controlling Program Flow

• T-SQL increases the control you have over the flow of program statements. The inclusion of “IF/ELSE” statements increases decision-making options, “WHILE” statements make it possible to iterate or repeat code statements in a looping pattern, “GOTO” statements give you options for transferring control from one program statement to another, and “WAITFOR” statements allow you to pause program actions.
  
  

Dealing With Errors

• T-SQL error handling includes three levels of system error notifications and the ability to create user-defined error rules. Built-in or system error types can be informational, warning messages or fatal errors. Add custom error codes or messages to the “sysmessages” error identification table, then use them just like system error messages. However, even in T-SQL, error handling does not occur automatically, so whether using system, custom or a combination of both in your code, you must first tell the database to check for errors.