Advanced SQL

Course Outline

Stored Procedure Basics

• Pros and cons of stored procedures
• Creating stored procedures
• Three ways to execute
• System stored procedures

Variables

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

Parameters and Return Values

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

Avoiding Scalar Functions

• What are scalar functions?
• Some examples
• Disadvantages of scalar functions
• Three advantages

Testing Conditions

• IF/ELSE statement
• Using CASE where possible

Looping

• Syntax of WHILE
• Breaking out of a loop
• Basic transactions
• Beginning a transaction
• Committing/rolling back
• Deleting and updating
• Using DELETE and UPDATE
• Sys.Objects
• Dropping objects

Creating Tables

• Creating Tables in SQL
• Inserting data
• Inserting single rows
• Inserting multiple rows

Temporary Tables and Table Variables

• Using temporary tables
• Creating table variables
• Pros and cons of each approach

Table-Valued Functions

• In-line table-valued functions
• Multi- Statement table-valued functions
• Limitations of user-defined functions

Derived Tables and CTEs

• Using derived tables
• Common Table Expressions (CTEs)
• Recursive CTEs

Subqueries

• The concept of a subquery
• Using ALL, ANY, and IN
• Correlated subqueries
• Using EXISTS

Cursors

• Syntax of fetching rows
• When to use (and when not to)

Error-Handling

• Using TRY/CATCH
• System error functions
• Custom error messages
• The obsolete @@ error function

Debugging

• Version differences
• The Visual Studio debugger
• The SQL Server debugger
• Debugging (breakpoints, etc.)