EssentialSQL: Stored Procedures Unpacked - Code in TSQL

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.

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.

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.