fbpx
Call us on  08000 199337 From overseas +44 (0)1344938011

Programming Microsoft SQL Server with Transact-SQL

Course Duration: 2 days

Book online today or if you need any assistance or help chosing the right course for you, please call our team on 08000 199337 and we will help point you in the right direction.

Virtual

Course Duration: 2 days

From £795.00 +Vat
View dates and book

Enquire Now

Classroom

Course Duration: 2 days

From +Vat
View dates and book

Enquire Now

About the Course

Course Duration: 2 days

Price From: £795


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”

Course dates

Start Date Exam Included Price (excl VAT) Qty  

Frequently asked questions

A Pearson VUE exam voucher enables you to book and sit your exam at your local Pearson VUE testing centre at a time and date convenient to you.  Pearson VUE centres are worldwide, and you will be able to choose the closest testing centre to you. You then go along to the test centre with your photo ID at the specified date and time and you will then take an electronic exam. Your exam voucher will have an expiration date and your exam must be sat before this date as these vouchers cannot be extended.

X