SQL Server training -  Advanced SQL

SQL Training

Advanced SQL (2 days)

£725 + VAT for most venues (£850 + VAT for London)

If you already know how to write SELECT statements in SQL, maybe it's time to find out how to program in SQL! Learn how to create stored procedures (including passing parameters to them), write your own user-defined functions, work with temporary tables and table variables and much more.

Course details

  • Locations for Advanced SQL coursesLocations:London, Manchester
  • Next Advanced SQL course locationNext course:Thu 16 March 2017
  • Advanced SQL course timesTime:09.30-16.30

Course Contents

Detailed stored procedures

  • Training topicPros and cons
  • Training topicCreating stored procedures
  • Training topicThree ways to execute
  • Training topicSystem stored procedures


  • Training topicDeclaring variables
  • Training topicSET versus SELECT
  • Training topicTricks with variables
  • Training topicSo-called global variables

Parameters and return values

  • Training topicPassing parameters
  • Training topicDefault values / WHERE clauses
  • Training topicOutput parameters
  • Training topicUsing RETURN

Scalar functions

  • Training topicWhat are scalar functions?
  • Training topicSome examples
  • Training topicDisadvantages of scalar functions
  • Training topicThree alternatives

Testing conditions

  • Training topicIF / ELSE statement
  • Training topicUsing CASE where possible


  • Training topicSyntax of WHILE
  • Training topicBreaking out of a loop


  • Training topicBeginning a transaction
  • Training topicCommitting / rolling back

Deleting and updating

  • Training topicUsing DELETE and UPDATE
  • Training topicSys.Objects
  • Training topicDropping objects

Creating tables

  • Training topicCreating tables in SQL
  • Training topicPrimary keys and indexes
  • Training topicSetting constraints
  • Training topicCreating from existing data

Inserting data

  • Training topicInserting single rows
  • Training topicInserting multiple rows

Temporary tables and table variables

  • Training topicUsing temporary tables
  • Training topicCreating table variables
  • Training topicPros and cons of each approach

Table-valued functions

  • Training topicIn-line table-valued functions
  • Training topicMult-statement table-valued functions
  • Training topicLimitations of user-defined functions

Derived tables and CTEs

  • Training topicUsing derived tables
  • Training topicCommon Table Expressions (CTEs)
  • Training topicRecursive CTEs


  • Training topicThe concept of a subquery
  • Training topicUsing ALL, ANY and IN
  • Training topicUsing EXISTS
  • Training topicCorrelated subqueries

Covered if time allows If time


  • Training topicSyntax of fetching rows
  • Training topicWhen not to use


  • Training topicUsing TRY / CATCH
  • Training topicSystem error functions
  • Training topicCustom error messages
  • Training topicThe obsolete @@error function


  • Training topicVersion differences
  • Training topicThe Visual Studio debugger
  • Training topicThe SQL Server debugger
  • Training topicDebugging (breakpoints, etc.)

Covered if time allows If time

Dynamic SQL

  • Training topicBuilding up dynamic SQL
  • Training topicExecuting dynamic SQL
  • Training topicPros and cons

Covered if time allows If time


  • Training topicUsing the PIVOT clause
  • Training topicDynamic pivots

Why Choose Us?

View full list of benefits View full list of benefits

Sample customer feedback

"Excellent trainer - very approachable and keen on answering questions from attendees. Course content covered what i needed - good exercises and helpful manual."

Daniele Bega (Department for Communities and Local Government)

"Content and pace were very good, lunch choices were also excellent."

Daniel Auld (Macquarie Energy Leasing)

"Plenty of useful ideas to take away for optimising queries. Found the use of the generic themes such as 'films' and 'websites' easy to convert to practical applications for my own business area of nursing & healthcare."

David Bench (Marie Curie)

"All the content covered was clear to understand. Well taught with great hospitality."

Jessica Sparkes (WorldPay UK Ltd)

"Plenty of great course material provided to keep me going."

Anthony Watson (Harrington Brooks)

There are lots more where these came from (or just refresh this page to change the quotes shown).

You should only attend this course if you have either attended our two-day introduction to SQL course or if you are comfortable creating queries using SQL.  The course shows how to write the dialect of SQL used by Microsoft (called T-SQL), using SQL Server Management Studio as a development environment.

Related Courses

Introduction to SQL


2 day course

Reporting Services RECOMMENDED


2 day course

Integration Services


2 day course

Other T-SQL programming training resources

SQL exercises

SQL exercises

Our advanced SQL exercises are based on simple, easy-to-understand databases, as you can see for yourself!

View SQL exercises

SQL videos

SQL videos

Although we firmly believe people learn best from people, we've also published a range of SQL tutorial videos online.

View SQL videos

SQL skills assessment

SQL skills assessment

You can see how well you know SQL relative to others by taking one of our skills assessment tests online.

View SQL skills assessment tests

SQL blogs

SQL blogs

Take a few minutes to see what blogs we've published on SQL on our website.

View SQL blogs

This page has 0 threads Add post