Stored Procedures Unpacked: Learn to Code T-SQL Stored Procs
4.4 (456 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,326 students enrolled

Stored Procedures Unpacked: Learn to Code T-SQL Stored Procs

Learn to develop, code, & implement stored procedures, transactions, & cursors, in your SQL Server database using T-SQL.
Bestseller
4.4 (456 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,326 students enrolled
Created by Kris Wenzel
Last updated 5/2020
English
English [Auto-generated]
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 3.5 hours on-demand video
  • 4 articles
  • 49 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Write bits of database code developers can call from other programs
  • Setup transactions, detect errors, and automatically rollback changes
  • Create reusable code to save time and make your queries easier to read
  • Use cursors to access specific rows withing a result.
  • Learn how to debug, that is troubleshoot, a stored procedure.
Course content
Expand all 39 lectures 03:41:15
+ Introduction
6 lectures 22:02

Welcome to Stored Procedures Unpacked.  My goal is to help take the mystery out of stored procedures and show you there is no magic to them at all!

By the time you have completed the course, you will have a thorough understanding of stored procedures, be able to write your own, and know when it’s best to use them.

Preview 02:24

Get to know what a stored procedure is the main purpose they serve in running T-SQL statements within your database.

Preview 06:41

I figured before we jump into the details, let's try to create a really simple stored procedure so you get a knack for how they're created and so that you see there isn't a "secret" to them.

Preview 07:57

Learning stored procedures is not  a linear learning process.  So, you'll see I show the big picture, then I dive into specifics.  Once you're exposed to many of the specific, especially in the programming concepts section, you start to write more comprehensive stored procedures.

Preview 04:04
Introduction Quiz
4 questions

Now that you've seen the lesson on creating your first stored procedure, let's try to create one ourselves.

Don't worry, this will be a really simple example to build confidence.

Let's get started!

Create Your Own Stored Procedure - Challenge
00:27

This is the solution to the challenge to "Create Your Own Stored Procedure."  Try the challenge first; no peeking!

Create Your Own Stored Procedure - Answer
00:29
+ Setup Your Learning Lab (Optional)
5 lectures 17:50

Check out the download resource.

Note:  Install SQL Server 2019 if you're running Windows 109; otherwise, use the guide and for SQL Server 2017.

Download SQL Server
02:16

Install SQL Server on your computer.

Install SQL Server
04:31

Install SQL Server Management Studio so that you can connect to the database and run queries.

Download and Install SSMS
03:45

We'll get the Microsoft sample databases.  Many of our examples are based on these. Let's get them installed on your computer so that you can also run the queries and learn by example.

The Links to samples are:

  • https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

  • https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0


Download Sample Databases
04:25

The last step!  Once complete your learning lab is complete! 

Restore Sample Databases
02:53
+ Stored Procedure Walk Through
4 lectures 25:53

Get acquainted with the walk through and understand the idea isn't to fully learn how to create a stored procedure in this lesson, it is to appreciate what they are so you can see the greater picture before we dive into details.

Introduction
00:56

There are some basic components you'll want to learn so you can get more familiar with stored procedure mechanics.

Parts of a Stored Procedure
03:55

Treat this lesson as an introductory lab.  Do worry if you don't understand every detail.  The idea is for you to start to see the whole picture, not necessarily study every detail.

Defining a Stored Procedure
20:38

I'm here to make your experience the best.  Let me know what I can do to help you.

Do you have any questions?
00:24
+ Debugging Stored Procedures
3 lectures 19:09

Debugging is a computer slang for troubleshooting a program.

What is Debugging?
03:19

This is an update to the original lesson.  Since SSMS no longer features an interactive debugger, I thought I would show you how I go about debugging stored procedures today.  I do this when working with stored procedures in Azure Synapse (Azure Data Warehouse) Cloud.

Interactive Debugging (Updated for SSMS 18)
08:21

In this lesson we'll learn how you can execute your store procedure one line at a time using the "step into" command.

Though this feature isn't available in SSMS 18.0 and later, it is good to review, as it shows you ho a modern debugger should work.  Also, it get you an idea of the execution path SQL takes when stepping though the stored procedures.


Note:

Since SSMS (SQL Server Management Studio) is free, I use this tool to teach SQL.  Before SSMS version 18 you could interactively debug your stored procedures.  Microsoft has taken this feature away for version 18.


My take:

Now that I work with Azure SQL Database and Azure Synapse Analytics, both of which are Microsoft databases withing the Azure cloud, I found you can not interactively debug stored procedure within these databases.  To compensate, I use lots of PRINT command when troubleshooting!


I go over this in the lesson titled "Interactive Debugging (Updated for SSMS 18)"

Interactive Debugging (SSMS 17 or Visual Studio)
07:29
Debugging Quiz
8 questions
+ Programming Concepts
8 lectures 56:56

Variable are used to store values.   Variables are used to store the results of a calculation.

Working with Variables
09:33

The PRINT command is used to send messages to the SSMS console.  It it a great way to show how examples work.

PRINT
03:22

BEGIN and END code block provide a means to bundle stored procedures together.  They let SQL know that the statement within a block should be run together.

BEGIN END Code Blocks
01:35

IF THEN logic is used to test a condition, and then based on the result of that test (TRUE or FALSE), switch code execution form one statement or code block to another.

IF THEN Logic
13:17

The WHILE command allows you to repeat block of code until a specific condition is met.

WHILE Loops
18:18

WAITFOR is used delay execution until a specific time or duration.

WAITFOR Statement
04:00

Use GOTO to jump to a label spot in code.  Watch out though, as having too many GOTOs can make your code hard to read.

GOTO and Labels
04:27

If you need to bail out of your stored procedure, use the RETURN command.  It immediate stop the current stored procedure's execution.

RETURN
02:24

This quiz covers the programming concepts covered within this section.

Programming Concepts Quiz
10 questions
Write a script to practice the programming concepts you learned in this section.
Programming Concepts Assignment
2 questions
+ Error Catching
2 lectures 12:37

The @@Error command is set each time a command runs.  Its value indicates whether the command successfully ran, or if there is an error.


You can test @@Error with an IF THEN statement.

Unstructured Error Handling using @@ERROR
04:31

TRY CATCH blocks are another way to trap errors.  When you use them, you're using structured error handling.

Structured Error Trapping with CATCH and TRY
08:06

Test your knowledge about structured and unstructured error handling.

Error Catching Quiz
3 questions
Write script to detect and react to errors.
Error Handling Assignment
2 questions
+ Creating and Executing Stored Procedures
5 lectures 28:51

Before we dig deep, let's go over the main components of a stored procedure and then build one together in the examples.

Overview
00:36

Parameters allow you to pass values into stored procedure so that they can be used in calculations or query criteria.  A typical example  is to lookup a record using a primary key.

Define a Stored Procedure with Parameters
06:56

A common use case is to use stored procedures to select results from a database.  These can be easily returned to the caller.  In the example we'll show you how to capture the result into a variable and then display its contents.

Returning Results with SELECT
05:26

You can pass stored procedure results back to the caller via OUTPUT parameters.

Using OUTPUT Parameters
08:39

Use return codes to indicate the status of a stored procedures processing.  For instance, if the stored procedure throws an error, you can use a status to indicate the type of error encountered.

Return Codes
07:14
Creating and Executing Stored Procedures Quiz
6 questions
Walk through the questions to build and execute a stored procedure
Creating and Executing Stored Procedures Assignment
2 questions
+ Cursors and Transactions
5 lectures 37:07

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 rolledback.

Transactions
09:10

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

Introduction to Cursors
08:07

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

ISO versus Non-ISO Cursor Syntax
05:41

The FETCH command is used to move forwards and backwards through a cursor's rows.  The most common use is FETCH NEXT.

FETCH Command
06:17

Walk though a comprehensive example to know and understand how to create a stored procedures which utilizes a cursor.

Case Study
07:52
Build a cursor to calculate min, max, and averages and then compare to non-cursor method to accomplish same task.
Cursors Assignment
3 questions
+ Wrap-up
1 lecture 00:49
Thank You and What's Next?
00:49
Requirements
  • You should already be familiar with basic queries, such as SELECT, GROUP BY, and ORDER BY clauses.
  • Knowledge of basic programming concepts, such as IF/THEN is a plus, but not mandatory.
Description

In this Course you will Learn to:

  • Use stored procedures to implement business logic.

  • Develop stored procedures from scratch!

  • Learn some basic  troubleshooting skills.

  • Know the right way to use cursors.

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

  • Trap and handle errors.

Nothing is worse than

  • Being excited to learn something new but not knowing where to start.

  • Wasting time learning the wrong features.

  • Being overwhelmed with options and not know which to use.

Imagine Having...

  • Knowledge - Knowing how to quickly write stored procedures to take your SQL to the next level.

  • Confidence - Feeling good that you’re on the right track.

  • Accomplishment - Having a sense of accomplishment that you've learned something most cannot.

  • Fun - Having fun learning about cursors, stored procedures, and triggers!

By the time you’ve complete this course you’ll have an appreciation of developing stored procedures, overcome your fear, and able to write stored procedures that pass parameters, handle errors, and rollback unintended database changes.

Now that’s cool!

Who this course is for:
  • Beginner SQL query authors curious about combining SQL statements into transactions.
  • Developers looking to for a better way to develop and manage database code.
  • Analysts looking to create advanced data sources from SQL Server from Power BI.
  • Developers wanting to learn about database cursors.
  • Anyone who thinks that stored procedures are really scary (they aren't!) :)