The Programming Microsoft SQL Server with Transact-SQL SQL training course introduces the delegate to the programming features of the Transact-SQL programming language, using Microsoft SQL Server. This course looks at the procedural programming language capabilities of T-SQL. The delegate will learn how to use local variables, write program flow control constructs, create stored procedures and triggers, handle errors and use built-in functions.

The delegate will learn and acquire skills as follows:

• Declaring and using variables
• Using conditional programming logic
• Implementing conditional execution and loop constructs
• Using cursors to process records
• Trapping and handling errors and exceptions in code
• Creating stored procedures
• Creating user defined functions
• Creating triggers
• Using dynamic SQL

Software development personnel and database support staff who need to write Transact-SQL scripts to enhance their productivity in manipulating data.

To provide the skills needed to write programs using the main features of the Microsoft Transact-SQL Programming Language.

A working knowledge of SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite Querying Microsoft SQL Server with Transact-SQL course. Some previous programming experience will also prove advantageous.

• Querying Microsoft SQL Server with Transact-SQL

• Advanced Querying Microsoft SQL Server with Transact-SQL

• Course technical content is subject to change without notice.
• Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions

Course Contents - DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: INTRODUCTION TO TRANSACT-SQL

• Procedural Programming Language
• Program structures
• Statement blocks
• Comments

Session 2: VARIABLES

• What is a variable
• Declaring a variable
• Variable names
• Available data types
• Variable scope
• Using the SET command
• Initialise a variable
• Display the values of variables
• Set variables in a query
• Assign values to variables in the UPDATE Statement

Session 3: CONDITIONAL LOGIC

• IF THEN ELSE statements
• Nested IF statements
• Specifying Conditions

Session 4: LOOPS AND OTHER CONTROL-OF-FLOW MECHANISMS

• The basic Loop construct
• The WHILE loop
• The BREAK statement
• The CONTINUE statement
• The GOTO statement
• The WAITFOR statement

Session 5: PROCESSING RECORDS WITH CURSORS

• What is a cursor?
• Cursor operations
• Declare a cursor
• Local and global cursors
• Open a cursor and fetch a row
• Cursor status checking with @@FETCH_STATUS
• Close and deallocate a cursor
• @@CURSOR_ROWS
• Use Cursor variables
• The FOR UPDATE and WHERE CURRENT OF statements
• Declare cursor options
• Cursor operation restrictions

Course Contents - DAY 2

Session 6: STORED PROCEDURES

• What is a stored procedure?
• The advantages of using stored procedures
• What is allowed and disallowed in a stored procedure
• Create a stored procedure
• Execute a stored procedure
• Use parameters
• Execute a procedure with parameters
• Use SET NOCOUNT ON
• Use a result set in an INSERT statement
• Set the return value of a procedure
• Output parameters
• View the source code of a stored procedure

Session 7: HANDLING ERRORS

• Handling errors
• The @@ERROR global function
• Using @@ERROR
• The TRY..CATCH statement
• The CATCH block
• Other error functions
• The RAISERROR statement

Session 8: USER DEFINED FUNCTIONS

• What is a user defined function?
• Creating user defined function
• Return a value from a user defined function
• Call a scalar function
• Table-Valued Functions
• Call table valued functions
• Inline table valued functions
• Multi-statement table valued functions

Session 9: TRIGGERS

• What is a trigger?
• How a trigger can be used
• Trigger types
• What can be done in trigger code?
• DML triggers and syntax
• The inserted and deleted tables
• The UPDATE()function
• COLUMNS_UPDATED
• Use INSTEAD OF triggers
• The INSTEAD OF trigger rules
• View Triggers
• DDL triggers
• Create a DDL triggers
• Notes on DDL trigger
• Logon triggers
• View trigger information
• Alter a trigger definition
• Disable, enable and drop triggers

Session 10: DYNAMIC SQL

• What is dynamic SQL?
• Create a dynamic SQL statement
• Prevent SQL injection

The Programming Microsoft SQL Server with Transact-SQL SQL training course introduces the delegate to the programming features of the Transact-SQL programming language, using Microsoft SQL Server. This course looks at the procedural programming language capabilities of T-SQL. The delegate will learn how to use local variables, write program flow control constructs, create stored procedures and triggers, handle errors and use built-in functions.

The delegate will learn and acquire skills as follows:

• Declaring and using variables
• Using conditional programming logic
• Implementing conditional execution and loop constructs
• Using cursors to process records
• Trapping and handling errors and exceptions in code
• Creating stored procedures
• Creating user defined functions
• Creating triggers
• Using dynamic SQL

Software development personnel and database support staff who need to write Transact-SQL scripts to enhance their productivity in manipulating data.

To provide the skills needed to write programs using the main features of the Microsoft Transact-SQL Programming Language.

A working knowledge of SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite Querying Microsoft SQL Server with Transact-SQL course. Some previous programming experience will also prove advantageous.

• Querying Microsoft SQL Server with Transact-SQL

• Advanced Querying Microsoft SQL Server with Transact-SQL

• Course technical content is subject to change without notice.
• Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Course Contents - DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: INTRODUCTION TO TRANSACT-SQL

• Procedural Programming Language
• Program structures
• Statement
  • blocks
  • Comments

  Session 2: VARIABLES

  • What is a variable
  • Declaring a variable
  • Variable names
  • Available data types
  • Variable scope
  • Using the SET command
  • Initialise a variable
  • Display the values of variables
  • Set variables in a query
  • Assign values to variables in the UPDATE Statement

  Session 3: CONDITIONAL LOGIC

  • IF THEN ELSE statements
  • Nested IF statements
  • Specifying Conditions

  Session 4: LOOPS AND OTHER CONTROL-OF-FLOW MECHANISMS

  • The basic Loop construct
  • The WHILE loop
  • The BREAK statement
  • The CONTINUE statement
  • The GOTO statement
  • The WAITFOR statement

  Session 5: PROCESSING RECORDS WITH CURSORS

  • What is a cursor?
  • Cursor operations
  • Declare a cursor
  • Local and global cursors
  • Open a cursor and fetch a row
  • Cursor status checking with @@FETCH_STATUS
  • Close and deallocate a cursor
  • @@CURSOR_ROWS
  • Use Cursor variables
  • The FOR UPDATE and WHERE CURRENT OF statements
  • Declare cursor options
  • Cursor operation restrictions

  Programming Microsoft SQL Server with Transact-SQL Training Course

  Course Contents - DAY 2

  Session 6: STORED PROCEDURES

  • What is a stored procedure?
  • The advantages of using stored procedures
  • What is allowed and disallowed in a stored procedure
  • Create a stored procedure
  • Execute a stored procedure
  • Use parameters
  • Execute a procedure with parameters
  • Use SET NOCOUNT ON
  • Use a result set in an INSERT statement
  • Set the return value of a procedure
  • Output parameters
  • View the source code of a stored procedure

  Session 7: HANDLING ERRORS

  • Handling errors
  • The @@ERROR global function
  • Using @@ERROR
  • The TRY..CATCH statement
  • The CATCH block
  • Other error functions
  • The RAISERROR statement

  Session 8: USER DEFINED FUNCTIONS

  • What is a user defined function?
  • Creating user defined function
  • Return a value from a user defined function
  • Call a scalar function
  • Table-Valued Functions
  • Call table valued functions
  • Inline table valued functions
  • Multi-statement table valued functions

  Session 9: TRIGGERS

  • What is a trigger?
  • How a trigger can be used
  • Trigger types
  • What can be done in trigger code?
  • DML triggers and syntax
  • The inserted and deleted tables
  • The UPDATE()function
  • COLUMNS_UPDATED
  • Use INSTEAD OF triggers
  • The INSTEAD OF trigger rules
  • View Triggers
  • DDL triggers
  • Create a DDL triggers
  • Notes on DDL trigger
  • Logon triggers
  • View trigger information
  • Alter a trigger definition
  • Disable, enable and drop triggers

  Session 10: DYNAMIC SQL

  • What is dynamic SQL?
  • Create a dynamic SQL statement
  • Prevent SQL injection

   

  ------------------------------------------------------------