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.
Next I'd like to talk about a real-world UPDATE stored procedure. On this one we're going to work with a slightly different set of business rules. Now in the real-world your insert and UPDATE stored procedure would likely have the same set of rules. I am going to go with a different set of business rules just for the sake of having more things to demo. So our update rules are you can not subscribe to the newsletter if your email address is null. This makes sense because our newsletter is an email newsletter. So you can't subscribe if you don't have an email address. The update stored procedure should not be allowed to change the ID.
It can change any other piece of data, but it cannot change someone's ID. It needs to return an error if the ID does not exist. If someone attempts to modify student 1234 but student 1234 does not exist in the database, we need to let them know about that problem, and then we have my general best practice return 1 for success and 0 for failure. So I have a stored procedure staged that should accomplish all of this and we'll start from the top. So we're going to CREATE PROCEDURE Students_Update.
One parameter for every field will take as an input, the ID will tell us which record we want a change, and everything else will represent the new values that were changing to. Scroll down a little. Lines 18 through 21 will count the number of times this ID exists in the database. If it comes out as 1, we will continue on, but if it comes out as anything other than 1 that's an error condition. So if the ID does not exist, then there's nothing to update. If the ID exists more than once we've got a bigger problem on our hands.
So we'll only continue if the ID exists once and exactly once. Line 30 to 32 implement the rule of you cannot subscribe to the newsletter if your email is null. This turned out to be a relatively small amount of code. It checks to see if email address is null, and if so it sets your subscription to off. You're not subscribed to the newsletter. Line 36 down is a big UPDATE statement that concludes with a where clause on line 47 where we're checking to make sure the ID that we are updating is in fact the ID that was passed to us.
Then similar as before, line 51 through 54 is checking to make sure we modified exactly 1 record that would indicate success. Anything other than modifying one record would indicate failure. So I'll run this, commands completed successfully. So I now have Students_Update and I have a little code staged that'll run this. So now let's go ahead and execute this stored procedure. Notice that I'm updating my own record. I'm setting my email address to null and I'm trying to subscribe to the newsletter.
I went ahead and put it 1 for my newsletter subscription, the stored procedure should catch that and change that to a 0. So I'll run this. It looks like success. Now let's go ahead and check that and there I am. My record is still intact, email has been set to null, and newsletter has been set to 0. So stored procedure is functioning as desired.
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.