TRANSACT-SQL: Variables and Programming

• SQL is a query language for relational database systems but lacks the power of programming languages
• SQL does not allow use of variables, flow control structures, loops, etc.
• SQL is ideal for working with databases and an additional tool is required for full application development that supports both SQL queries and the versatility of traditional programming languages
• TRANSACT-SQL is a programming language provided by Microsoft SQL Server to extend standard SQL with additional instructions and language-specific elements

TRANSACT-SQL Programming Construction

• TRANSACT-SQL expands standard SQL by implementing programming structures
• Programmers with experience in C++, Java, Visual Basic .NET, C#, and similar languages will find these implementations familiar

Variables

• A variable is an entity to which a value is assigned which can change during the process where the variable is used
• SQL Server has two variable types: local and global
• Local variables are defined by the user
• Global variables are supplied and predefined by the system

Local Variables

• Local variables are declared, named, and written using the keyword declare
• An initial value is assigned via a select or set instruction
• The names of local variables must begin with the “@” symbol
• Each local variable must be assigned a data type defined by the user or supplied by the system
• Syntax to declare a variable: DECLARE @VARIABLE <DATA TYPE> to assign a value to a variable SET @VARIABLE= VALUE
• A SELECT instruction can be used instead of SET
• A SELECT instruction used to assign values to one or more variables is called an assignment SELECT
• When using assignment SELECT, it cannot return values to the client as a set of results

Public Variables

• Global variables are predefined variables supplied by the system, distinguished from local variables by having two “@” symbols
• Some server global variables include:
  • @@ERROR: Contains 0 if the last transaction was executed correctly
  • @@IDENTITY: Contains the last value inserted into an IDENTITY column through an insert instruction
  • @@VERSION: Returns the SQL Server version
  • @@SERVERNAME: Returns the server name
  • @@LANGUAGE: Returns the language name in use
  • @@MAX_CONNECTIONS: Returns the maximum number of allowed connections

Flow control tools

• Flow control languages may be used with interactive statements in batches and stored procedures
• Flow control and related keywords and functions include:
  • IF ... ELSE: Defines conditional execution if the condition is true or an alternative if the condition is false
  • CASE: A simple way to make If-type operations
  • WHILE: Repetitive structure that executes a block of instructions while the condition is true
  • BEGIN ... END: Defines a block of instructions, that executes a set of instructions
  • DECLARE: Declares local variables
  • BREAK: Exits the end of the nearest inner while loop
  • ...CONTINUE: Restarts the while loop
  • RETURN [n]: Unconditionally exits often used in stored or triggered procedures
  • PRINT: Prints a user-defined message or a local variable on the user’s screen
  • /*COMMENT*/ or SQL – COMMENT: Inserts a comment at any point in an SQL instruction

IF Control Structures

• The IF keyword is used to define a condition that determines whether the next instruction will be executed
• The SQL instruction is executed if the condition is met, i.e. if it returns TRUE
• The ELSE keyword introduces an alternative SQL instruction that executes when the IF condition returns FALSE

Conditional CASE Structure

• The CASE structure evaluates a list of conditions and returns one of several possible result expressions with two formats:
  • Simple CASE-expression: Compares an expression with a set of simple expressions to determine the result
  • Searched CASE-expression: Evaluates a set of Boolean expressions to determine the result
• Both formats accept an optional ELSE argument
• CASE <expression> WHEN <expression_value> THEN <returned_value> WHEN <expression_value1> THEN <returned_value1> ELSE <returned_value2> END
• The CASE structure can be used in any instruction/clause allowing a valid expression such as SELECT, UPDATE, DELETE, and SET instructions, and select_list, IN, WHERE, ORDER BY, and HAVING clauses
• The CASE function is a special Transact-SQL expression for showing an alternative value depending on a column

Search CASE Expression

• The search CASE expression in a SELECT instruction allows substituting values in the result set based on comparison values

WHILE Control Structure

• The WHILE structure repetitively executes a set/block of SQL instructions as long as the specified condition is true
• Execution of instructions in the WHILE loop can be controlled with the BREAK and CONTINUE keywords
• The syntax of WHILE is: WHILE <expression> BEGIN ... END

BREAK and CONTINUE

• BREAK and CONTINUE control the functioning of instructions within a WHILE loop
• BREAK allows exiting the WHILE loop
• CONTINUE causes the WHILE loop to start again
• Syntax for BREAK and CONTINUE: WHILE BOOLEAN_EXPRESSION BEGIN EXPRESSION_SQL [BREAK] [EXPRESSION_SQL] END [CONTINUE] [EXPRESSION_SQL]

Error Control in TRANSACT-SQL

• SQL Server provides error control through TRY and CATCH instructions which mark a step forward in SQL Server error control with syntax: BEGIN TRY EXPRESSION_SQL END TRY BEGIN CATCH EXPRESSION_SQL END CATCH

Special Error Handling Functions

• Special error functions are exclusively available in the CATCH block to obtain detailed error information
• Functions used in error control include:
  • ERROR_NUMBER(): Returns the error number
  • ERROR_SEVERITY(): Returns the error severity
  • ERROR_STATE(): Returns the error state
  • ERROR_PROCEDURE(): Returns the stored procedure name that caused the error
  • ERROR_LINE(): Returns the line number where the error occurred
  • ERROR_MESSAGE(): Returns the error message

System Variable @@ERROR

• @@ERROR returns the last executed TRANSACT-SQL instruction’s error number
• If the variable returns 0, the prior TRANSACT-SQL found no errors
• If the error can be found on the sys.sysmessages catalog view, @@ERROR will contain the sys.sysmessages.error column’s value for said error
• The text associated with the error number can be viewed in sys.sysmessages.description

RAISERROR to Generate Errors

• An error may be voluntarily triggered when the data fails to comply with a business rule
• The RAISERROR function receives three parameters: the error message, the severity, and the state
• Severity indicates the error’s degree of criticality, admitting values from 0 to 25
  • Values from 0 to 18 may be assigned
  • Values from 20 to 25 are considered fatal by the system and will close the connection executing the RAISERROR command
  • Assigning values from 19 to 25 requires being a member of the SQL Server sysadmin function

Cursors in TRANSACT-SQL

• A cursor is a variable that loops through a result set obtained through a SELECT statement row by row
• Using cursors is a technique that handles query results row by row, unlike SELECT SQL, which handles a set of rows
• Cursors can be implemented through TRANSACT-SQL instructions (ANSI-SQL cursors) or the OLE-DB API
• ANSI cursors are used when it is necessary to handle the rows individually in a set or when SQL can only act on the affected rows
• API cursors are used by client applications for handling significant volumes or managing various result sets
• Best practice steps when using cursors:
  • Declare the cursor, using DECLARE
  • Open the cursor, using OPEN
  • Read the cursor's data, using FETCH ... INTO
  • Close the cursor, using CLOSE
  • Free the cursor, using DEALLOCATE

Cursor Operations

• Declaration: DECLARE <CURSOR_NAME> CURSOR FOR <SQL_STATEMENT>
• Opening the cursor: OPEN <CURSOR_NAME>
• Retrieving the first row of the cursor
• Syntax for syntax to define the declaration of the ANSI cursor: DECLARE <CURSOR_NAME> [INSENSITIVE][SCROLL] CURSOR FOR <SQL_STATEMENT> FOR [READ ONLY | UPDATE[OF COLUMN_LIST]]
  • INSENSITIVE: Only operations on the next row are allowed
  • SCROLL: movement in cursor rows can be done in all directions
  • UPDATE: Specifies that updates are made on the cursor's source table
• The TRANSACT-SQL proposes extended syntax to define cursors: DECLARE <cursor_name> [LOCAL | GLOBAL]
  • LOCAL: the cursor’s scope is local to the whole batch
  • GLOBAL: The cursor’s scope is global to the connection
  • By default to local cursor database option is defined as false
• Extraction of data from cursor based of the order of appearance is done through FOWARD_ONLY
• Creation of a temporary copy of the data in the data base for the cursor is done through STATIC
• KEYSET sets the rows and their order in the cursor at the time of the cursor’s opening
• DYNAMIC means the cursor exactly reflects the database's present data: the number of rows, their order, and their value may vary dynamically
• You can define a Forward Only cursor through FAST_FORWARD
• Guarantee the success of UPDATE and DELETE instructions through SCROLL_LOCKS
• OPTIMISTIC means, an UPDATE or DELETE operation performed on the cursor may not be correctly executed, should another transaction modify the data in parallel
• TYPE_WARNING sends a client application a warning message should implicit type conversions take place
• Opening an cursor requires OPEN [GLOBAL ] <cursor_name>

Registering Cursors

•  FETCH extracts a row from the cursor and assigns values to variables with its content
• After FETCH, the @@FETCH_STATUS variable is set to 0 if FETCH returns no errors
• FETCH [NEXT | PRIOR | LAST | ABSOLUTE n | RELATIVE n ][FROM] [GLOBAL ] <cursor_name>
• NEXT: reads the next row (The only possible option for INSENSITIVE CURSOR)
• PRIOR: Reads the previous line
• FIRST: Reads the first row
• LAST: Reads the last row
• ABSOLUTE n: Reads the nth row of the set
• RELATIVE n: Reads the nth row from the current row

Cursor Functions

• The @@FETCH_STATUS function indicates the status of the last issued FETCH instruction; possible values include:
  • 0: The FETCH instruction was successfully executed
  • -1: The FETCH instruction was not successfully executed or the row was beyond the result set
  • -2: The retrieved row is missing
• Closing the Cursor: Closes the cursor and frees the memory
• This operation should be interposed as soon as possible to free resources sooner
• Syntax for closing the cursor: CLOSE <cursor_name>
• Once the cursor is closed, it can no longer be captured or updated/eliminated
• All keys are removed when closing the cursor, leaving the cursor definition intact and the closed cursor can be reopened without being re-declared
• Freeing Resources: A cleanup command that is not part of the ANSI specification with syntax: DEALLOCATE <cursor_name>