STORED PROCEDURES UNPACKED

Before I Tell You All About This Life-Changing Program, Let's Talk About Who This is Really For...

You are in the right place if you’re an ambitious professional who want to learn stored procedures to expand their SQL skill set, open job opportunities, and advance their career.

Whether You're...

• A developer looking to secure your app with stored procedures.
• A consultant looking to expand  your tool set
• An IT professional looking to read and understand database code
• An analyst looking to parametrize PowerBI queries

You're in the right place and I'm going to tell you exactly how Stored Procedure Unpacked gives you the tools, inspiration, and unparalleled support on your journey to level up your SQL skills.

By the end of this program,

you will have…

• Used stored procedures to implement business logic.

• Developed stored procedures from scratch!

• Learned basic  troubleshooting and debugging skills.

• Learned the right way to use cursors.

• Applied database transaction techniques to commit or rollback your database changes.

• Trapped and handled errors.

• Gained a valuable skill set to help you advance your career.

"This course is what I'm looking for! I'm a Production DBA, but I want to learn to write complex Stored Procedures.

By learning and practicing the lessons in the course, I'm able to understand the T-SQL codes that our developers wrote and as well as to contribute ideas when it comes to troubleshooting codes.

Furthermore, I will be more effective in code reviews if ever they require a DBA."

RICKY VALENCIA, DATABASE ADMINISRATOR

What’s Inside Stored Procedures Unpacked 

Module 1

Introduction

Before we dig in to the details, we get a quick win learn about stored procedures, and then creating our first one!

Module Highlights:

• What is a stored procedure? - I'll make sure you get the basic, have you covered.

• Creating Your First Stored Procedure - We go through the steps to create our first working stored procedure in the first module.  Why?  This is to get you context.  Once we dig into the details, this overview become a welcome reference.

Module 2

Introduction and Learning Lab Setup

Before we dig in, I want to make sure you have all the tools ready to learn SQL.  In this section we will go over the course set up, and then get your learning lab set up on your computer.

Module Highlights:

• Setup Learning lab - I’ll show you how to get a free copy of SQL server and get it setup on your computer.  We’ll use this in conjunction with our example database to do the challenge problems.

• Step-By-Step Guide - Being we're coding programming stored procedures, its recommend you have access to the AdventureWorks database.  I'll show you how to get it setup!

Module 3

Stored Procedure Walk Through

In this module you'll learn about the main part of a stored procedure and the commands used to define a stored procedure.

Module Highlights:

• Learn about a stored procedure's, input, execution, and output components.

• Walk through the evolution of a query from simple statement to stored procedure.

Module 4

Debugging Stored Procedures

Know how to find you programming mistakes will save you hours, tears, and your sanity! In this module I'll show you how to use some SQL tool to "peek" inside your stored procedure to understand the steps it is taking.  They may not be the ones you think...

Module Highlights:

• Learn how to use the Visual Studio Debugger.

• Run your stored procedures statement by statement.

• See variable values

Module 5

Programming Concepts

In this module we'll cover all the basics of stored procedure control of flow. You'll learn how to define variables, and then use them in logic statement to determine what queries to perform next.
Every modle is accompanied with an assignment and quiz to ensure you understand the material.
This module is a course onto itself!

Module Highlights:

• Working With Variables - Declare and Set variable values.
• PRINT - Learn to send text and variable values to the messages window.  This is useful for troubleshooting.
• Control of Flow - Learn "Control of Flow" statement such as IF-THEN and WHILE to guide your stored procedure's execution.
• You learn the following Statements:
  • DECLARE, SET
  • PRINT
  • BEGIN, END
  • IF-THEN ELSE
  • WHILE, BREAK, CONTINUE
  • WAITFOR
  • GOTO, Label:
  • RETURN

Module 6

Error Catching

When things don't don't go to plan, it good to know how to trap errors and gracefully handle them within your stored procedure.
In this module we take a look at two main methods, structured and unstructured error handling, you'll use to trap and and handle execution exceptions.

Module Highlights:

• Learn how to catch errors in your stored procedures and handle them using CATCH TRY statements and the function used to describe the error messages and severity.

• Learn about unstructured programming. Though now CATCH/TRY is preferred, it still important to be able to read older code.

Module 7

Creating and Executing Stored Procedures

Learn how to call stored procedures with parameters.  Pass values into the stored procedure as well as out of it.  Learn about the difference between an output parameter and return code.

Module Highlights:

• Learn about the three main way you can return values from a stored procedure: result, parameter, return code.

• Learn how to define parameters to pass into and out of your stored procedure.

• Learn the proper way to use return codes.

Module 8

Cursors and Transactions

Once you complete this module you'll know how to set up a database transactions and cursors to iterate through a result to commit or rollback updates, depending upon detected errors.

Module Highlights:

• Explore how database transactions ensure multiple updates complete as a set before they are committed to the database, of if there's trouble, all changes can be rolled back.

• Understand what a cursor is and why they are different from traditional SQL results.

• Cursors defined using ISO syntax adhere to a standard used across many database platforms.