Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The process of creating a stored procedure is quite similar to that of doing a function, or indeed of defining any procedure, function, or method in any other programming language. We're going to name it. We have to give it a name. We're going to define any parameters that it takes. We're going to actually describe what it does, which in our case will be run either a single SQL statement or even batches of T-SQL. And then return any values, describe what it is that gets sent back. So how do we do that? Well, luckily, SQL Server Management Studio has some helpers for us.
If I actually drill down into the database in which I want to create my stored procedure, I'll use AdventureWorksLT, I'll open up the Programmability section and we see that we have the Stored Procedures folder right beside the Functions folder. I'll open that guy up. I'll right-click the Stored Procedures folder and say New Stored Procedure. Again, we get a lot of boilerplate code. Just to make things clearer, I could get rid of some of the comments. I'm going to just ignore the settings here for ANSI_NULLS and QUOTED_IDENTIFIER. You just as a general rule leave those alone and then what we have is this.
We have the CREATE PROCEDURE. Give it a name. Define the parameters if it takes any. If it doesn't, you can just scrap that. Then describe what it is the stored procedure does, which by itself if it's got SELECT statements is also defining what it returns. So, creating the simplest stored procedure in the world, let's give it a name. Well, we do have to put a schema for it, which could be the usual schema of SalesLT. Or quite commonly you'll also seem under dbo.
For our case, it really doesn't matter. We just have to give it a name. I'll call this uspSimple, a user stored procedure. Very simple one. I'm going to make one that takes no parameters, so I can just delete those lines. I'm going to leave the usual message here of NOCOUNT ON. We'll talk about that in a second, and then I'm going to describe what actually happens in the stored procedure. Well, we could be doing INSERTs, we could be doing UPDATEs, we could be doing DELETEs, or we could just be doing standard SELECTs.
I could just type in a very straightforward one. SELECT * From SalesLT.Customer. Perhaps that's what I want the stored procedure to be. It's a little bit of SQL, wrapped up and given a name. So let me create it. I'll run this SQL right now. Again, this SQL is creating the stored procedure. So when I say Command(s) completed successfully, I don't expect to see any results yet.
We've now made the stored procedure. We just haven't run it. If I go over to the Object Explorer, it doesn't show up and that's because I need to refresh it. So I'll come up here to the Refresh button and then I can see, yes, I've got dbo.uspSimple. Great, looks good. I'll close these two files down. I don't need them anymore, and open up a new query window so I can actually run this. Well, we don't use the word SELECT. If we're running a stored procedure, we do this. We say EXEC for execute.
I could type in dbo.uspSimple. That's not showing up in my IntelliSense right now, because the IntelliSense just hasn't refreshed itself. But we should be able to get this to work. If I hit F5, yep, it does the same thing as would have happened if I had manually typed SELECT * FROM SalesLT.Customer. If I get annoyed about the red squiggly lines that it couldn't find that stored procedure, I can either restart SQL Server Management Studio, or with my cursor in this SQL box, I can go to the Edit menu, click IntelliSense, and just say Refresh Local Cache.
That takes a second and then the red squiggly should go away. Yes of course, this is the simplest stored procedure on the planet. You're not really going to be doing things like this all the time, although it's possible. If I want to change that, what I do is go back to the Object Explorer. I'll right-click that stored procedure and click Modify. It brings up a very similar SQL, except changing the CREATE PROCEDURE word to ALTER PROCEDURE. I'll just make my changes and run this file again.
You can also execute it by right clicking the stored procedure directly. Execute Stored Procedure. It throws up a dialog box here that gives you the opportunity to type in any parameters you might use. In our case, we don't have any. So I'll just click OK and we get the generated code to run a stored procedure here. There is a little bit more than we need, because it's setting up for return values, which we don't really care about. But that's the general process. So, what if I want to do something that's a bit more complex? Well, rather than type it out bit by bit, I'm going to use one of the few pre-created files that I have here.
I have got a similar looking file here to create a stored procedure, except this has a bit more to it. In this one, what I'm actually doing is inserting three rows into three different tables. Now, the question is why? Well, here is the deal. If I come over to my AdventureWorksLT, I have three tables called ProductDescription, ProductModel, and ProductModelProductDescription. What's this about? Well, the idea is that in this database if you're inserting a new ProductModel, you basically just insert the name.
If I took a look at this, I can see that there are things like Classic Vest, Cycling Cap, Half-Finger Gloves, Road Frame, and so on. Well, the idea is that this table is going to support multiple international languages potentially. So, I'm not just going to put in a description straight for that ProductModel. I have a separate ProductDescription table. If I look at that one, I can see that I have certain descriptions that are being stored. But as I come down, I've actually got some odd text showing up, because we're actually using multiple languages here.
We've got French. We have got some of the big five languages. Now the issue is that we could have different descriptions for the product models and potentially with descriptions like Chromoly steel, we can have different product models for the descriptions. So we've got a many to many relationship going on here. That's what this third table represents. PublicModelProductDescription. Well all this one really does is hook up a particular ProductModelID from the ProductModel table, to a ProductDescriptionID from the ProductDescription table, and then adding the Culture.
So we could have English, we could have French, we could have Thai. Okay, even if that seems a little vague right now, it's a very common situation. When you're inserting some information, all I want to do is I've just got some new ProductModel and Description information. I don't really care about how many tables have to be created and how many rows I need to do and in what order I need to do the rows. But of course, it is drastically important. If I want to make this work, I have to insert the model, then the description, and get both of the keys that have been generated before I can insert the third one.
So this is a great thing for a stored procedure. In fact, that's what this stored procedure example does. It takes three parameters. A productname that will be used to go into the ProductModel table, a productdescription that will be used to go into the ProductDescription table, and a culture, which will be used to go into the one that joins them together. In fact, culture I've set up here with an equal sign. That means this is a default value. I don't have to pass that information into the stored procedure.
The idea is I can call the stored procedure once, give it a couple of pieces of information, and end up with three rows. So breaking it down a bit further. Inside the contents of the stored procedure, I define two variables that are going to hold the keys of the two inserted rows. So in a moment, we'll insert a new ProductModel and we'll grab the productmodelid. Then we'll insert a new ProductDescription and we'll grab the generated productdescriptionid. Then we'll take both of those and insert them into ProductModelDescription.
So, step one is we insert the customer row with the parameter of productname. Then we use of this line, SELECT @productmodelid, that's our variable that we defined, = @@IDENTITY. This is a useful function that will actually grab the last inserted identity field. Step two, we do the equivalent for the ProductDescription table, inserting Description, and then we grab its IDENTITY into our productdescriptionid.
Then finally, step three. We do our third INSERT statement. Inserting into the join table, the ProductModelID, the ProductDescription, and the Culture. So let me run this SQL to create this stored procedure. Execute, Command(s) are completed successfully. All right, well, let's find out. I open up a new query. I'm going to refresh my IntelliSense. Make sure I'm using AdventureWorksLT.
Then I'll say EXEC uspCreateModelAndDescription. It's telling me here that I should be giving it a couple of parameters. The one that's highlighted in bold says "Well, give me the productname." That should be in nvarchar. I'll call it Groovy new product. I hit the comma and it highlights the next one, productdescription. Groovy new product description. If I wanted to, I could add a different culture.
If I did the comma, it would tell me that I should put in a string for the culture. But you know, I'm not going to. I'm just going to leave that blank, because there is a default value of en for English. Well, let's give this stored procedure call a try. I hit Execute. Command completed successfully. Okay, looks good. Let's prove it. The quickest way is going to be, let's take a look at the entire table, right-click, Select Top 1000 Rows. Zoom right down to the bottom and we see that we have a Groovy new product inserted with a ProductModelID of 130.
Take a look at the ProductDescription. Select that Top 1000 Rows. All the way down to the bottom and we have a Groovy new product description of 2012. So what I'm hoping to see now is that in ProductModelProductDescription. I will see 130 looking up to 2012 with a culture of en. Right down at the bottom, that's what we have. 130 key looking up to ProductDescriptionID of 2012 with the Culture of en and all of the default values generated as they should be.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.