OpenCourser
Learn skills that shape your future
Visit OpenCourser.com
Use TSQL to write stored procedures and scripts to automate data engineering and data analysis task. This is a great SQL course for a DBA to take if they want to stand out from other database administrators. Learn Database Programming concepts using SQL Server stored procedures.
Whether You're:
Use TSQL to write stored procedures and scripts to automate data engineering and data analysis task. This is a great SQL course for a DBA to take if they want to stand out from other database administrators. Learn Database Programming concepts using SQL Server stored procedures.
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 for SQL data analysis
A sql server DBA looking to build their SQL server database administrator skills
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.
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 the knowledge to know how to quickly write stored procedures to take your SQL to the next level. The confidence confidence that you're on the right track. A sense of accomplishment, you have learned something most cannot.
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.
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.
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!
This is the solution to the challenge to "Create Your Own Stored Procedure." Try the challenge first; no peeking!
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.
Install SQL Server on your computer.
Install SQL Server Management Studio so that you can connect to the database and run queries.
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
The last step! Once complete your learning lab is complete!
Interactive debugging is a great way to "peek" into your stored procedure's operation. I used it throughout the course to show you how elements work.
You'll want to use it to troubleshoot common programming mistakes.
Unfortunately interactive debugging isn't included with SSMS (SQL Server Management Studio), so we'll get another tool, name Visual Studio, to help.
Goto https://visualstudio.microsoft.com/ get the FREE download.
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.
There are some basic components you'll want to learn so you can get more familiar with stored procedure mechanics.
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.
I'm here to make your experience the best. Let me know what I can do to help you.
Debugging is a computer slang for troubleshooting a program.
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, you can still used from within Visual Studio.
We'll learn to use variables as well as how to display them using PRINT.
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.
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.
The WHILE command allows you to repeat block of code until a specific condition is met.
Understand basic while loop structure by seeing how to use the WHILE command to count to 10.
Understand how while loops can help leverage patterns and avoid repetitive scripting.
Use the BREAK command in a while loop.
WAITFOR is used delay execution until a specific time or duration.
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.
If you need to bail out of your stored procedure, use the RETURN command. It immediate stop the current stored procedure's execution.
This quiz covers the programming concepts covered within this section.
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.
You'll be able to write a stored procedure to trap an error using @@ERROR.
TRY CATCH blocks are another way to trap errors. When you use them, you're using structured error handling.
Understand statement flow for TRY CATCH and also see that you can use TRY CATCH to define overall stored procedure blocks.
Test your knowledge about structured and unstructured error handling.
See how to set up log table to log "caught" errors. Once caught, "rethrow" errors using THROW statement.
Before we dig deep, let's go over the main components of a stored procedure and then build one together in the examples.
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.
Follow along as define and use input and output parameters.
Use stored procedure parameters to encapsulate logic to create handy utility you can use later one for error trapping and general log writing.
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.
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.
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.
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.
The FETCH command is used to move forwards and backwards through a cursor's rows. The most common use is FETCH NEXT.
Walk though a comprehensive example to know and understand how to create a stored procedures which utilizes a cursor.
Save
Comprehensive coverage of debugging, error handling, and transactions.
Instructor excels at explaining complex topics simply.
Strong focus on real-world use cases and hands-on demos.
SaveOpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.
Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.
Find this site helpful? Tell a friend about us.
We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.
Your purchases help us maintain our catalog and keep our servers humming without ads.
Thank you for supporting OpenCourser.
