empower your people,
empower your organization
Call:  08000 199337 From overseas call +44 20 7264 2185
face-to-face training
Training Since 2002
Online Learning
Certified Trainers with 20+ Year's Experience
Virtual classroom training
Above Industry Average Pass Rates
7-key IT disciplines
No Fee Course Resits

Enjoy live training & exams NOW from your own home Learn more Now offering Remotely Proctored ExamsLearn More

Programming Microsoft SQL Server with Transact-SQL

About the Course

Course Duration: 2 days

Price From: £750


Private Course: Click Here
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.

Exam

There is no exam requirement for this course.

Course Objectives

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

Syllabus – Key points

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

Reviews

There are no reviews yet.

Be the first to review “Programming Microsoft SQL Server with Transact-SQL”

Your email address will not be published. Required fields are marked *

View dates and book

Select the Programming Microsoft SQL Server with Transact-SQL Classroom & Virtual Course

Now offering VIRTUAL courses. Enjoy live training NOW from your home Learn more
Start DateExam IncludedPrice
(Excl VAT)
Qty 
Start Date: Feb 17, 2022Exam Included: Course + No ExamPrice (Excl VAT):

£750.00

Quantity:
Start Date: Apr 19, 2022Exam Included: Course + No ExamPrice (Excl VAT):

£750.00

Quantity:
Start Date: Jun 20, 2022Exam Included: Course + No ExamPrice (Excl VAT):

£750.00

Quantity:
Start Date: Aug 18, 2022Exam Included: Course + No ExamPrice (Excl VAT):

£750.00

Quantity:
Clear

Types of Exams

Pearson Vue Voucher Exams

Online Exam

Classroom Exam

OUR CUSTOMERS INCLUDE

OUR LOCATIONS

We operate from ten training centres across the country, giving you more choice for where to attend

  • Birmingham
  • Bristol
  • London
  • Milton Keynes
  • Reading
  • Borehamwood
  • Leeds
  • Manchester
  • Nottingham
  • Southampton