T-SQL Programming

                   T-SQL stands for TRANSACT-SQL. It is a programming language, which can be used to develop logic on the database. 

Local Variables:

A local variable can be created by using the DECLARE statement. An initial value can be assigned to the variable with the help of the SELECT statement and can be used within the trigger or procedure where it is created or assigned the value.

The syntax is:

DECLARE @variable_name data_type [, @variable_name data_type]

Text Box: DECLARE @name variable char (20)
DECLARE @age int

Initializing Variables:
We can make use of either SELECT or SET statements to initialize variables.


SELECT @varname=value  (or)
SET @varname=value


SELECT @name_variable=’AKINOVA KURENDIOL’, @age=22



Global Variables:

Global variables are pre-defined and maintained by the system. The server to track server-wide and session-specific information uses them. They cannot be explicitly set or declared. Global variables cannot be defined by users and are not used to pass information across processors by applications. Many of the global variables report on system activity since the last time SQL server was started; other report information about a connection.

Some common global variables are:

Global Variable


Returns the number of rows processed by preceding command.
Returns the error number of the last error generated.
Returns the transaction nesting level status.
Returns the name of the local SQL server.
Returns the version of the SQL server using.
Returns the current process ID.
Returns the last identity value used in an insert.
Returns the number of level nested in a stored procedure/trigger.
Returns the status of the previous fetch statement in cursor.

PRINT Statement

The PRINT statement is used to pass a message to the client program’s message handler. It is used to display user-defined messages.

The syntax is:

PRINT character_string|@local_variable|@@global_variable

The message to be displayed using PRINT statement can be up to 255 characters long.

DECLARE @myname char(20)
          SELECT @myname=’JOJO ALAMENTO’
          SELECT ‘My Name is’ + @myname
          PRINT @myname



When series of statements need to be executed it is better to enclose them in blocks. SQL server provides the BEGIN…END block for this purpose and the statements enclosed between BEGIN and END block are known as statement block. Statement blocks are used with IF…ELSE and WHILE control –of-flow language. If BEGIN and END are not used, only the first statement that immediately follows IF…ELSE or WHILE is executed.

The command syntax is:

          {sql_statement | statement_block}


Statements to be executed conditionally are identified with the IF…ELSE construct. The IF…ELSE block allows a statement or statement block to be executed when a condition is TRUE  or FALSE.

The command syntax is:

IF Boolean_expression
                   {sql_staement | statement_block}
[ELSE Boolean_expression
          {sql_statement | statement_block}]


IF…ELSE constructs acan be used in batches, in stored procedures and in ad hoc queries. IF tests can be nested, either after another IF or following and ELSE.  There is no limit to the number of nesting levels.

Consider the following example:

USE pubs
          IF (SELECT SUM(price) FROM titles WHERE type=’business’) < 600
PRINT ‘ the sum of the following  BUSINESS books is less than 60 Dollars’
FROM titles
WHERE type=’business’




          In situations where you need a large number of IF statements, SQL Server provides a CASE statement. The CASE statement enables multiple possible conditions to be managed within a SELECT statement.

The syntax is:

                   WHEN Boolean_expression THEN expression1
                   [[WHEN Boolean_expression THEN expression][..]
                   [ELSE expression]


SELECT type=
                   CASE  type
                             WHEN ‘BUSINESS’ THEN ‘BUSINESS BOOK’
                             WHEN ‘mad_cook’ THEN ‘MODERN COOKING’
                             WHEN ‘trad_cook’ THEN ‘TRADITIONAL COOKING’
                             WHEN ‘psychology’ THEN ‘PSYCHOLOGY BOOK’
                             ELSE  ‘No category assigned as yet’
                   ‘Average Price’=AVG(price)
                   ‘Average Advance=AVG (advance)
          FROM titles
WHERE title_id LIKE ‘bu%” OR title_id LIKE ‘MC% OR title_id LIKE ‘PC%’ OR title_id LIKE ‘PS%’
GROUP BY type.


The WHILE construct is useful when repeated execution of SQL statements(s) is required. The statements are executed repeatedly as long as the specified condition is true.
SQL Server provides BREAK and CONTINUE statements to control the loop from inside of the WHILE construct.

The command syntax is:

WHILE Boolean_expression
                   {sql_statement | statement_block}
                   {sql_statement | statement_block}

WHILE (SELECT AVG (price) FROM titles) < $60
                   SELECT title FROM titles
                   IF (SELECT MAX (price) FROM titles) > $30


Post a Comment