How to Properly Set a bookID Variable in a SQL Stored Procedure

Описание: Learn how to effectively set a variable from a table cell in SQL stored procedures and avoid common errors like MSG 201.
---
This video is based on the question https://stackoverflow.com/q/65920093/ asked by the user 'Kudue' ( https://stackoverflow.com/u/15091639/ ) and on the answer https://stackoverflow.com/a/65920450/ provided by the user 'Thom A' ( https://stackoverflow.com/u/2029983/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL: Set variable to a cell value inside a stored procedure

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Solving the Problem of Setting a Variable from a Cell Value in SQL Stored Procedures

When working with SQL stored procedures, you may encounter situations where you need to store a cell value from one table into a variable. This operation is useful when the retrieved information will be used to update a value in another table. However, if not handled correctly, you might face errors like MSG 201: "Procedure or function expects parameter '@ bookID', which was not supplied." In this post, we’ll explore how to properly set a variable from a cell value and address common pitfalls.

The Problem Statement

You have a stored procedure designed to record the return of a borrowed book in your database. This stored procedure, named spBookReturn, attempts to:

Update the status of the book in the BorrowedMaterial table.

Retrieve the bookID based on a provided loanID.

Use the bookID to increase the count of available copies in the Books table.

However, you encountered an issue because when the stored procedure was executed, it raised an error indicating that the @ bookID parameter was not supplied.

Example of the Issue

[[See Video to Reveal this Text or Code Snippet]]

When you executed the procedure like this:

[[See Video to Reveal this Text or Code Snippet]]

You received the error.

Understanding the Source of the Error

The error arises from the way the parameters are defined. Here's what you need to consider:

All parameters for a procedure are input parameters: Even if you declare a parameter as OUTPUT, it still acts as an input unless you provide a default value.

Optional Output Parameters: To make OUTPUT parameters optional, default values should be assigned, preventing the necessity to always supply them.

Suggested Solution

Here’s a revised version of your stored procedure that includes essential error handling and correct parameter definitions:

[[See Video to Reveal this Text or Code Snippet]]

Execution of the Procedure

Now you can execute the stored procedure without needing to supply values for @ bookID and @ custID, like so:

[[See Video to Reveal this Text or Code Snippet]]

Final Thoughts

By making sure that your parameters are properly defined as outputs and using optional default values, you can avoid the frustrating MSG 201 error. Proper transaction handling with TRY...CATCH also helps to maintain data integrity during the updates. With these changes, your stored procedure should work smoothly, allowing you to manage returned books effectively in your database.

Next time you find yourself struggling with SQL stored procedures, remember these best practices to save time and hassle!