Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
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.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.