Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
I mentioned earlier to you a recommendation that as a best practice; every stored procedure return a value indicating success or failure. Typically, we return a 0 for failure and the 1 for success. We haven't yet implemented that. So let's go ahead and work on it. I'll change this code that creates a stored procedure into a code that alters a stored procedure. The last allow will be RETURN 1 for success. We also need to define some failure condition. As a hypothetical, we're going to say that if the first name is too short, let's say extremely short, less than two characters, we'll define that as a failure condition.
Check for the if len(@FirstName). If it's less than 2 characters, that is now defined as a failure condition. So we'll need to RETURN a 0 to indicate this stored procedure was unsuccessful. And as soon as it hits that line of RETURN 0, it will not execute the bottom. It will not do the insert. Command(s) completed successfully. That's good news. Now we need to go over to our website and do a little more work. In order to accept that return value we're going to have to create a new parameter.
So websites accept return values from stored procedures as a special type of parameter. I have some code already written in your exercise files. I'm going to insert that now. There is one chunk that goes right before the connection.open and another chunk they goes right after connection.close. Now let's look at what this code does. The top part creates a new parameter called returnParameter, and it sets an unusual direction on that. For every parameter we have the choice to set a direction.
Our choices are Input, Output, both Input and Output, or the one I'm going to choose ReturnValue. And this line just adds the parameter to the existing commands. Below the close, I added some code that reads the value of the return value and checks to see if it's equal to 0. If it is in fact equal to 0, that is an error condition and I'm going to put a label on the screen that will hold the message this is an error in the stored procedure. So I'll need to come back over to the graphical port and drag a Label right next to the button.
I'll go ahead and run this, and I'm going to intentionally enter some bad data. So for the first name I'll make sure does in fact too short and we receive the message there was an error in the stored procedure. It did not do the insert and instead did what we expected. It returned a 0 to us indicating the stored procedure had failed. So this follows a common best practice of using a 0 to indicate failure, a 1 to indicate success, and every stored procedure should always return either success or failure.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.