T-SQL

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]
 
 




         
Example:
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.

Syntax:




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




         
         
Example:





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

Description

@@rowcount
Returns the number of rows processed by preceding command.
@@error
Returns the error number of the last error generated.
@@trancount
Returns the transaction nesting level status.
@@servername
Returns the name of the local SQL server.
@@version
Returns the version of the SQL server using.
@@spid
Returns the current process ID.
@@identity
Returns the last identity value used in an insert.
@@nestlevel
Returns the number of level nested in a stored procedure/trigger.
@@sqlstatus
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
GO    
 
         







CONTROL-OF-FLOW LANGUAGE:

BEGIN…END


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:




BEGIN
          {sql_statement | statement_block}
END
 
 






IF…ELSE 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
          BEGIN
PRINT ‘ the sum of the following  BUSINESS books is less than 60 Dollars’
PRINT ‘’
FROM titles
WHERE type=’business’
          END
 
         








 

 

CASE CONSTRUCT


          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:
         




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















Example:



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’
                   END
                   ‘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.
 
         

















WHILE CONSTRUCT


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}
                   [BREAK]
                   {sql_statement | statement_block}
                   [CONTINUE]

Example:
         
WHILE (SELECT AVG (price) FROM titles) < $60
          BEGIN
                   SELECT title FROM titles
                   IF (SELECT MAX (price) FROM titles) > $30
                                      BREAK
                   ELSE
                                      CONTINUE
          END
 
 





















0 comments:

Post a Comment