Advanced SQL (classroom and online)

• Creating stored procedures
• Executing stored procedures
• System stored procedures

• Declaring variables
• SET versus SELECT
• Tricks with variables
• So-called global variables

• Passing parameters
• Default values / WHERE clauses
• Output parameters
• Using RETURN

• What they are
• Writing user-defined functions
• Worked examples
• Pros and cons of scalar functions

• IF / ELSE statement
• Using CASE where possible

• Syntax of WHILE
• Breaking out of a loop

• Using TRY / CATCH
• System error functions
• Custom error messages

• Ways to drop tables
• DELETE versus TRUNCATE

• The UPDATE statement
• Updating using joins

• Making tables (SELECT INTO)
• Appending data (INSERT INTO)
• Inserting individual rows

• Creating tables in SQL
• Primary keys and indexes
• Adding relationships

• Beginning a transaction
• Committing / rolling back

• Scope (local versus global)
• Using temporary tables
• Creating table variables
• Pros and cons of each approach

• In-line table-valued functions
• Mult-statement table-valued functions

• Using derived tables
• Common Table Expressions (CTEs)
• Multiple CTEs in a single query

If time

• Syntax of fetching rows
• Why not to use cursors!

• Debugging queries and procedures
• Setting breakpoints

If time

• Building up dynamic SQL
• Executing dynamic SQL
• Disadvantages of dynamic SQL

If time

• Assembling data for pivoting
• Using PIVOT
• Dynamic column headers

If time

• Insert, update and delete triggers
• Using the generated tables (eg INSERTED)
• Using INSTEAD OF