So I'm going to take this stored procedure a little further. The stored procedure I'm working with right now has three INSERT statements. It inserts an entry into the ProductModel table, it inserts a ProductDescription, and then it inserts a row that hopefully joins these two new rows together. Okay, so let's see it work. I'm going to flip over to where I have a prewritten piece of SQL that will execute that stored procedure, passing in ABCD123, and ABCD123 product description.
So I execute that code. We get a result, the Command(s) completed successfully. To double check that, I have a little bit of code over here that's simply selecting the most recently added row to each of those three tables. I'll run that. And I get what I hoped for, that the first row that was added was for ProductModelID 139, so that primary key was generated, ABCD123. We have the Description that's under 2016, and ABCD123 product description, and then we have the third row that is joining 139 to the first row and 2016 to the second.
So everything looks correctly happened and in the right order. Here is the issue. What happens if I run that again? Well, it should work just fine, right? I execute it. Well, we have a problem going on here. The message isn't all that friendly, but it's telling me there is a Violation of UNIQUE KEY constraint. Cannot insert duplicate key in object SalesLT.ProductModel. Maybe I'm getting this message and I'm the developer and I know what that means. It's not the friendliest thing in the world, and here is the issue. If I go back and run this code again to see the most recently added records, what I'm going to see is that the row for ProductModel will nothing got added with that second call there.
The issue is that there is a constraint on the table that says this name must be unique. So we didn't get a new entry. Having said that, the problem is we did get a new row for the Description. Because the last time around, it was 2016. We now have a new row in there with this Description, but it won't be connected to anything because we couldn't insert the first one, and because we didn't get the first one done, we didn't get the third one done either, because there was no ProductModelID to insert. So they're all now out of step.
We've got a bit of a problem. We have this orphaned record hanging around. We'd have to go in and manually hook up. Well, what can we do with that? Well, really the deal is this. I want my stored procedure to work completely. I want either all three of these inserts to occur correctly or not at all. There are a couple of steps of doing that. Let's do step one. It's very, very simple. What I'm going to do is surround my three INSERT statements with BEGIN TRAN.
It doesn't have to be in uppercase, but that's by convention, and COMMIT TRAN. So we're beginning the transaction. We're going to try and do the first INSERT, then the second INSERT, then the third INSERT. Hopefully, by the time we get past it, we're going to commit those changes to the database. So this is just the edited SQL for altering that stored procedures. So I'm going to execute that. Command completed successfully, looks good. Let's go over to this and we're going to shake up this entry a little bit.
Let's say we're now trying to enter in a couple of other things here. We're entering a model description for DEF123, just so we can be sure that that's unique. I'll execute that. Command completed successfully. Go back over and check what the three most recent rows were. We have 141 for DEF123, 2018 for DEF123, and then 141 and 2018 joined. Looks good. Now, here is the proof. Let's try and run this because we have the transaction around them. I execute it.
Okay, we have the error. That's to be expected. Just having a transaction doesn't mean that there wasn't a problem. We have a problem. We're trying to insert a duplicate key into the ProductModel. Here is the question though. What happened? Well, if I go over and run this code again, we're actually running into this situation. 141. Okay, that was the one from the previous time. We didn't get one inserted there, but we did get a new one inserted here, because if you look at the last entry on the joining table, it was 141 to 2018.
We've now got 141, but we have a 2019. So, what's going on? Here is the problem. Just because we said that we wanted to work with the transactions and we surrounded our three statements with this BEGIN TRAN and COMMIT TRAN, well, that doesn't really mean anything. Because just because there was a problem, well, we didn't actually tell it that we wanted to roll anything back. So here is what we do instead. Now to make this a little bit more readable, I'm just going to get rid of a couple of the blank lines here.
Before I do my BEGIN TRAN, I'm going to write this. BEGIN TRY and END TRY. Now these officially don't have to be nested, but to make it a bit more readable, I'll do that. So we open up what's called a TRY block. Our TRY block means we're trying things from the opening of the TRY to the end of the TRY. That's where we're trying to do our transaction. The flipside of that is that if there is a problem, what happens is our TRY block is going to leap out and go looking for a CATCH block.
What I need to do in here is say this. I want to ROLLBACK the transaction, which will just reverse anything that happened inside the BEGIN TRAN and COMMIT TRAN keywords. What would also be useful is if I do a little bit of the tracing information. I'll print out a message that says there was an error. That won't be all that helpful so I better output a little bit more and this is the way I would do that. I'm going to use one of the built-in functions called ERROR_MESSAGE ().
This only works if you use ERROR_MESSAGE inside a CATCH block. It's only actually ever going to have anything if there was a problem. So this has changed the stored procedure. Now I'm going to of course execute that to make sure that the database knows that the stored procedure has been changed. So again, we need to go back and test it and check it. I'm going to jump across here in the first try and make one that works. Let's verify that everything is actually working okay. We're going to try and enter in the GHI123 and GHI123 product description. Execute.
Come over and check the recently retrieved records, and we have 143, GHI123. 2020, GHI123. [00:07:20.00 ] 143, 2020. So the hope that I would have is if I go back and run that stored procedure exactly the same as it was, then when I come back to this page, I see exactly the same thing. I don't suddenly get an extra ProductDescription row in there. Let's go and find out. We jump across.
Again, I am executing this again. Just because we have a transaction doesn't mean there isn't a problem. There is. Execute. There we go. We can see that we jump to our CATCH block. I've got there was an error. Then the message that pops out says Violation of UNIQUE KEY constraint, Cannot insert duplicate key in object SalesLT.ProductModel. Same problem. We want to see if there is a different behavior now. So hopefully, we'll see 143, 2020, and 143, 2020 when I execute this. We run it, and yes indeed, 143, 2020, 143 and 2020.
Because of having those BEGIN TRAN and COMMIT TRAN inside the TRY block, and then catching it, rolling it back, and printing out the message, we've managed to roll back all those changes. So, very easy to start to wrap your different blocks of SQL with that TRY and TRAN in a very controllable way of handling that stuff.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74803 Viewers
80 Video lessons · 129869 Viewers
52 Video lessons · 64070 Viewers
59 Video lessons · 49865 Viewers
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.
Your file was successfully uploaded.