Programming Microsoft SQL Server with Transact-SQL

Course Code: TSQLP

Duration: 2 days

 
 
 
 

Programming Microsoft SQL Server with Transact-SQL Course Overview

The Programming Microsoft SQL Server with Transact-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.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Course Objectives

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

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.

Skills Gained

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

Programming Microsoft SQL Server with Transact-SQL Training Course

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

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
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.

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

Course Reviews

Well structured course with great learning examples.

Megan - IT Developer - NHS - January 2024

Public Scheduled Events

Classroom & Live Virtual Instructor-Led Training

Duration: 2 days

Price: £900.00 exc. VAT 


Start Date Options Spaces  
16 May 2024
StayAhead Virtual Courses available 
Spaces Book Now 
22 Jul 2024
StayAhead Virtual Courses available 
Spaces Book Now 
07 Oct 2024
StayAhead Virtual Courses available 
Spaces Book Now 
16 Dec 2024
StayAhead Virtual Courses available 
Spaces Book Now 
 

Live Virtual Classroom

 
Join live instructor-led classroom training from the comfort of your home or office.
All the convenience and benefits of the classroom experience without the hassle and costs of travel and accommodation.
 
 



Our Customers Include

 
EDF
Amazon
American Express
Aviva
QA
BAE
University of Cambridge
Barnardo's
Scottish Government
Bauer
Bloomberg
BP
HSBC
DVLA
GlaxoSmithKline
Government Campus
Capita
Tui
NHS
Ordnance Survey
Ministry of Defence
Zurich Insurance Group
trainline
Vodafone
 
 



Our Course Curriculum

 
 
 
+44 (0)20 7600 6116
Enquiries@StayAhead.com
Copyright © 2024 StayAhead Training Ltd
Cookies   /   Privacy Policy