We may earn an affiliate commission when you visit our partners.
Course image
Kris Wenzel and Essential Sql

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.

Read more

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.

Enroll now

What's inside

Learning objectives

  • Understand what a stored procedure is and how it differs from other types of sql queries.
  • Understand how to pass parameters to and from stored procedures.
  • Know how to use variables and control structures such as if/else and while loops within stored procedures.
  • Learn how to debug, that is troubleshoot, a stored procedure.
  • Setup transactions, detect errors, and automatically rollback changes
  • Understand how to use cursors in stored procedures.

Syllabus

By the end of this section you'll know why we need stored procedures. We'll even create a really simple on so you get to know the process.

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.

Read more

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.

Introduction Quiz

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!

Students will have set up their own learning lab. Meaning, you'll have a complete setup of SQLServer on your computer with the most popular example databases. It's awesome!

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.

After finishing this session you'll know the main parts of a stored procedure and create one. This is so you'll appreciate some of the details coming up in later sections.

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.

It's crucial that you learn how to troubleshoot stored procedures. In this section we'll talk about ways you can execute a stored procedure and see how it executes line by line.

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.

Debugging Assignment (Walk Through)
Debugging Quiz
Know how to use control of flow statements such as IF and WHILE to influence stored procedure program execution.

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.

Programming using IF THEN

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.

Programming While Loops - Counting by Twos

Understand how while loops can help leverage patterns and avoid repetitive scripting.

Use the BREAK command in a while loop.

Programming While Loops - Processing Strings

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.

Programming Concepts Assignment
Error Catching

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.

Error Handling Assignment

See how to set up log table to log "caught" errors.  Once caught, "rethrow" errors using THROW statement.

Error Handling and Logging Assignment
By the end of this section you'll know how to run a stored procedures, pass parameters values into the proc, as well as receive results as either OUTPUT parameters, query results, or a return code.

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.

Using LogWrite in a Stored Procedure

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.

Creating and Executing Stored Procedures Quiz
Creating and Executing Stored Procedures Assignment
Once you complete this sections 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.

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.

Cursors Assignment
Wrap-up
Bonus Lecture

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Suitable for database administrators looking to advance their skills and stand out in their field
Provides a solid foundation for beginners in database programming and stored procedures
Helpful for IT professionals seeking to expand their toolset and improve their understanding of database code
Ideal for developers aiming to enhance security in their applications through stored procedures
Useful for analysts looking to optimize PowerBI queries for more efficient data analysis

Save this course

Save EssentialSQL: Stored Procedures Unpacked - Code in TSQL to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in EssentialSQL: Stored Procedures Unpacked - Code in TSQL with these activities:
Database Systems: The Complete Book
Read and review the foundational concepts of database systems, providing a solid theoretical understanding to support your course learning.
Show steps
  • Read through the chapters covering the basic concepts of database systems.
  • Complete the exercises and review questions at the end of each chapter to reinforce your understanding.
  • Discuss the key concepts with peers or the instructor to clarify and deepen your knowledge.
SQL Queries and Database Management
Review the fundamentals of SQL queries and database management concepts to strengthen your foundation for this course.
Browse courses on SQL Queries
Show steps
  • Review notes and materials from previous SQL courses or tutorials.
  • Complete practice exercises on writing SQL queries and creating database tables.
  • Set up a local database environment for practicing queries and database operations.
Udemy Course: Database Programming Concepts Using SQL Server Stored Procedures
Learn and apply programming concepts in a structured, step-by-step tutorial
Browse courses on Programming Concepts
Show steps
  • Enroll in the Udemy Course
  • Watch the video lectures and complete the exercises
  • Implement the concepts in your own projects
Seven other activities
Expand to see all activities and additional details
Show all ten activities
Create a Simple Stored Procedure
Follow guided tutorials to create a simple stored procedure, familiarizing yourself with the basic syntax and structure.
Browse courses on Stored Procedures
Show steps
  • Find a tutorial on creating stored procedures in SQL Server.
  • Follow the steps in the tutorial to create your first stored procedure.
  • Test your stored procedure by calling it from a query.
Debug stored procedures step by step
Strengthen command over stored procedure debugging techniques
Browse courses on Programming Concepts
Show steps
  • Install Visual Studio if you don't already have it
  • Set up a breakpoint and step into a stored procedure line by line
  • Inspect values and modify data to test and refine logic
  • Repeat until stored procedure operates as intended
Collaborative Database Problem-Solving
Engage in peer-led discussions and group problem-solving sessions to tackle challenging database scenarios, fostering critical thinking and collaboration.
Browse courses on Database Troubleshooting
Show steps
  • Join or form a study group with peers.
  • Present a database problem or issue to the group.
  • Brainstorm and discuss potential solutions collaboratively.
  • Implement and test the proposed solutions.
Develop a stored procedure to solve a practical problem
Apply stored procedure knowledge and problem-solving skills to build a practical solution
Browse courses on Problem Solving
Show steps
  • Identify a problem that can be solved using a stored procedure
  • Design the stored procedure
  • Implement the stored procedure
  • Test and debug the stored procedure
  • Deploy and use the stored procedure to solve the problem
SQL Query Optimization
Practice optimizing SQL queries to improve performance and efficiency, enhancing your understanding of query execution plans.
Browse courses on Query Optimization
Show steps
  • Analyze slow-running queries using tools like SQL Server Profiler.
  • Identify performance bottlenecks and apply appropriate indexing techniques.
  • Test and compare the performance of different query optimization techniques.
Database Performance Tuning Workshop
Attend a workshop focused on database performance tuning techniques, gaining hands-on experience and practical knowledge to optimize database performance.
Browse courses on Database Performance
Show steps
  • Register and attend a database performance tuning workshop.
  • Participate actively in the hands-on exercises and demonstrations.
  • Apply the learned techniques to improve the performance of your own databases.
Database Design Documentation
Create a comprehensive database design document that outlines the structure, relationships, and constraints of your database, solidifying your understanding of data modeling principles.
Browse courses on Database Design
Show steps
  • Identify the entities and attributes involved in the database.
  • Establish relationships between the entities using appropriate data models (e.g., ERD).
  • Define constraints and rules to ensure data integrity and consistency.
  • Document the design in a clear and concise manner using tools like Visio or Draw.io.

Career center

Learners who complete EssentialSQL: Stored Procedures Unpacked - Code in TSQL will develop knowledge and skills that may be useful to these careers:
Database Administrator
A Database Administrator (DBA) is responsible for the maintenance and administration of databases. This course can help build a foundation for a career as a DBA by teaching learners how to write stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures. This knowledge is essential for DBAs who need to be able to manage and maintain databases efficiently.
Data Analyst
A Data Analyst is responsible for collecting, analyzing, and interpreting data to help businesses make informed decisions. This course can help build a foundation for a career as a Data Analyst by teaching learners how to write stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures. This knowledge is essential for Data Analysts who need to be able to work with data efficiently and effectively.
Business Analyst
A Business Analyst is responsible for understanding the business needs of an organization and translating those needs into technical requirements. This course can help build a foundation for a career as a Business Analyst by teaching learners how to write stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures. This knowledge is essential for Business Analysts who need to be able to communicate with both business and technical stakeholders.
Software Engineer
A Software Engineer is responsible for designing, developing, and maintaining software applications. This course may be useful for Software Engineers who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Data Scientist
A Data Scientist is responsible for using data to solve business problems. This course may be useful for Data Scientists who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
IT Consultant
An IT Consultant is responsible for providing advice and guidance to businesses on how to use technology to improve their operations. This course may be useful for IT Consultants who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Database Developer
A Database Developer is responsible for designing and developing databases. This course can help build a foundation for a career as a Database Developer by teaching learners how to write stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures. This knowledge is essential for Database Developers who need to be able to design and develop databases that are efficient and effective.
Systems Analyst
A Systems Analyst is responsible for analyzing and designing computer systems. This course may be useful for Systems Analysts who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Information Security Analyst
An Information Security Analyst is responsible for protecting an organization's data and information systems from unauthorized access, use, disclosure, disruption, modification, or destruction. This course may be useful for Information Security Analysts who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Network Administrator
A Network Administrator is responsible for managing and maintaining an organization's computer networks. This course may be useful for Network Administrators who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Computer Programmer
A Computer Programmer is responsible for writing and maintaining computer programs. This course may be useful for Computer Programmers who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Web Developer
A Web Developer is responsible for designing and developing websites. This course may be useful for Web Developers who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Data Entry Clerk
A Data Entry Clerk is responsible for entering data into a computer system. This course may be useful for Data Entry Clerks who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Office Administrator
An Office Administrator is responsible for providing administrative support to an organization. This course may be useful for Office Administrators who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.
Customer Service Representative
A Customer Service Representative is responsible for providing customer service to customers. This course may be useful for Customer Service Representatives who want to learn how to use stored procedures and scripts to automate data engineering and data analysis tasks. Specifically, the course covers how to use TSQL to write stored procedures and scripts, how to pass parameters to and from stored procedures, and how to use variables and control structures such as IF/ELSE and WHILE loops within stored procedures.

Reading list

We've selected nine books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in EssentialSQL: Stored Procedures Unpacked - Code in TSQL.
A comprehensive textbook on database systems, covering topics such as data models, query processing, transaction management, and database design. It valuable reference for learners who want to gain a deeper understanding of the theoretical foundations of databases.
A deep dive into the internal workings of SQL Server, covering topics such as memory management, lock escalation, and query optimization. It valuable resource for experienced SQL Server professionals who want to gain a deeper understanding of the database engine.
A comprehensive guide to SQL Server database recovery, covering topics such as disaster recovery, point-in-time recovery, and log shipping. It valuable resource for database administrators who need to ensure the availability and integrity of their databases.
A detailed guide to understanding and optimizing SQL Server execution plans, which are the visual representations of how SQL Server executes queries. It is useful for learners who want to improve the performance of their SQL queries.
A detailed guide to SQL performance tuning, covering topics such as query optimization, indexing, and hardware optimization. It valuable resource for learners who want to improve the performance of their SQL queries.
A beginner-friendly guide to SQL, covering the fundamentals of data querying, data manipulation, and database design. It good starting point for learners who have no prior SQL experience.
A visual and interactive guide to SQL, using a conversational and humorous approach. It good choice for learners who prefer a more engaging and less technical introduction to SQL.

Share

Help others find this course page by sharing it with your friends and followers:

Similar courses

Here are nine courses similar to EssentialSQL: Stored Procedures Unpacked - Code in TSQL.
Programming SQL Server Database Stored Procedures
Most relevant
Automating Data Cleansing in SQL Server
Most relevant
Automate Data Workflows with SQL Server Stored Procedures
Most relevant
Optimizing SQL Server Procedures: Best Practices and...
Most relevant
Learn SQL Queries
Most relevant
Capturing Logic with Stored Procedures in T-SQL
Most relevant
C# Remoting : Build Client / Server Database Applications
Most relevant
How To Begin Your Career As a SQL Server DBA
Most relevant
Prepared Statements and Stored Procedures
Most relevant
Our mission

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

Affiliate disclosure

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.

© 2016 - 2024 OpenCourser