Programming Microsoft SQL Server with Transact-SQL
Course Code: TSQLP
Duration: 2 days
Our training courses can also be delivered at a location of your choice...
Programming Microsoft SQL Server with Transact-SQL Course Overview
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.
Course Objectives
To provide the skills needed to write programs using the main features of the Microsoft Transact-SQL Programming Language.
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
Follow-On Courses
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.
Course Contents
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
You can attend your training virtually using the London dates shown above.
Please contact us for more details on our Programming Microsoft SQL Server with Transact-SQL
Duration: 2 days
RRP: £750.00 exc. VAT
Upcoming Scheduled Events
Next 6 months
London | |||
|---|---|---|---|
| Start Date | Spaces | Book | |
| 16 Sep 2019 |
Spaces
|
Book | |
| 21 Nov 2019 |
Spaces
|
Book | |
| 30 Jan 2020 |
Spaces
|
Book | |
| 14 Apr 2020 |
Spaces
|
Book | |
| 18 Jun 2020 |
Spaces
|
Book | |
You can attend your training virtually using the London dates shown above.
Please contact us for more details on our Virtual training options or to discuss specific Virtual course dates to meet your requirements.
Course Deliveries
Public Scheduled and Closed
Virtual
UK and Overseas
Oracle User Group - Membership Benefits
Certification
Our independent Oracle, Solaris & Red Hat Linux curriculums helps prepare delegates for official certification.
StayAhead
Flexibility
Cannot see a sutiable date?
Please call us and we will try an accommodateyour needs!
Our Course Curriculum
Java, JavaScript, Web & Web Services
Programming Languages
Data Science
Database Training
Oracle Database & e-Business Suite
Business Analysis, ITIL & Prince2
Cloud & Cyber Security
Our Ratings
Instructor
Quality
97.31%
Course
Practical
92.53%
Overall
Courseware
94.13%
Overall
Course
96.29%