Programming Microsoft SQL Server with Transact-SQL

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