Stored Procedures Unpacked: Learn to Code T-SQL Stored Procs
- 3.5 hours on-demand video
- 4 articles
- 49 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- 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.
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.
Get to know what a stored procedure is the main purpose they serve in running T-SQL statements within your database.
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.
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.
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:
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.
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.
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.
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)"
- 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.
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.
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!
- 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!) :)