Overview:

About the course

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

Who will the course benefit?

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

Entry:

Requirements

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.

Pre-Requisite Courses

  • Querying Microsoft SQL Server with Transact-SQL

Follow-On Courses

  • Advanced Querying Microsoft SQL Server with Transact-SQL

Notes

  • 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.
Objectives:

Course Objectives

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

Syllabus:

Syllabus

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
2 Days

Course Dates

There are no dates scheduled for this location at the moment. Please call to enquire 08000 199337
 

 

Select a Classroom Course

Course Title Start Date Finish Date Location Price Qty
Programming Microsoft SQL Server with Transact-SQL Jan 10, 2019 Jan 11, 2019 London £650
Course Type:


Location:

Session Dates: