Procedure using a simple select statement as the basis we'll show you a couple of ways of executing your store procedures how you can modify and make changes to a procedure later on and.
Finally how you can delete a procedure that you no longer need so let's get started a stored procedure is simply a group of SQL statements grouped together under a single heading they're similar.
To methods or subroutines in other programming languages you might have encountered they've got several benefits but for me the main ones are their speed and efficiency so just a quick example.
Of that I've got a basic query here that's been written to show me a list of films and some basic aggregate statistics about their running time in minutes when I'm group by clause and.
Times on a regular basis I haven't need to write out this set of code again and again and again or I need to save the file and then reopen it and run it each time I need to get the results with a.
Stored procedure however if I've created one as I have earlier I can execute that stored procedure or that set of code in a much more simple and quick easy fashion execute this page here I get.
Exactly the same set of results but without having to either open another file or write out all that code again so in this tutorial we're going to show you the absolute basics of how you can get.
To this stage and a simple stored procedure that you can execute for our first example we're going to use this basic select statement to create our first stall procedure and you can do.
This with literally any select statement that you've written doesn't matter how complicated it is to use it to create a stored procedure all I need to do is write a create procedure statement above.
Posts Related:
The Select statement you can actually
Also get away with just writing quick proc which is what I usually do is I'm a bit lazy like that after the word proc or procedure you'd only think of a.Sensible name for your procedure I always star mine with letters sp hopefully for obvious reasons and this is going to be a basic film list so SP film list the only thing you have to do.
Then is write the word as I usually do this on a separate line and then that will convert the select statement below into a stall procedure when we execute this code before I do execute this page.
To create my stored procedure is worth mentioning a couple of other things after the as keyword it's often useful to write a begin and end block to contain all of the statements that.
Belong to your store procedure it's not actually necessary in this case because we only have one simple select statement put in more complex procedures where you're writing many many statements the.
Beginning the M block groups all of the useful statements together in one block another useful thing we can do as well is right to use statement above the create statement to make sure that.
You're going to create the procedure in the appropriate database so you may have seen new statements before you can write them above above simple select clean lines as well so I'm going to say to use.
Movies now one problem with the use statement and the create procedure statement is that the create procedure statement must be the first statement in a batch and at this point use movies is.
Currently the first statement in the batch the way to solve that little problem is to write a go statement which begins a new batch of statements that's a quick comment there begins a new batch.
So there we go
Although many of these things aren't actually necessary you don't have to write the use statement you don't have to write the beginning the end block.They're useful things to write in it's worthwhile getting into that habit now that we've written out all the code all the remains is to execute this script to create the store procedure not.
Only that in the standard ways I would normally execute a query and the thing you're looking for over the obviously his commands completed successfully down in the messages window at the bottom.
Notice they don't actually see the results of this like statement itself and this this message refers to the fact that the procedure has been created properly so let's just check that that's.
Actually happened and to find your store procedure you can look in the object Explorer and open it in place this for the moment and you'll need to look into your movies and database all the.
Database in which you've created the procedure and looked for the programmability folder in there should be a folder called store procedures I'm going to see there's a few in here.
Already and in fact there's the one that I've just created one called SP film list it's worth mentioning that this list doesn't always refresh immediately so sometimes when you come to look at.
The store procedures list the one you've just created isn't actually displayed in that case what you can do is select the database in which you've created the stored procedure and then manually.
Refresh it using the refresh button that's of the object Explorer and you'll then have to re-expand the programmability folder and the store procedures folder that's usually enough.
To force your stored procedure to appear once you've created the procedure you've got several choices for how you can execute it if you still got the page open which you use to create the.
Procedure in the first place in really straightforward way with such a basic procedure you can simply highlight the name of it and then execute that and in the bottom in the results pane you'll.
See the results of the stored procedure now hopefully obviously in the long run you won't have this page still available once you create the procedure you simply usually discard this so.
Normally what you would do is have a new query window in which you type in the word exact or execute if you prefer and then the name of your stored procedure SP film list now annoyingly if you're.
Using the intellisense you may well see that the name of the stored procedure is underlined in red similarly because the the intellisense list hasn't get refreshed if it annoys you then you.
Can head to the Edit menu choose intellisense and then choose the Refresh local cache or just press ctrl + Shift + R on your keyboard and for a brief pause that.
Usually clears out the problem it would have worked anyway and I executed this and but sometimes it annoys annoys people when they see the red squiggly in the lines anyway now.
That you've written out this simple statements you can execute this page and again that will execute all of the code is stored in the store procedure so now that you've seen how to create and.
Execute a basic stored procedure what happens if you'd like to modify it and change the way that it works a couple of simple ways that you can do that if you still have this the scripts.
Open which you used to create the procedure in the first place you can simply head back to this one and let's see well let's make a really simple change let's change the the order.
By Clause so the source our films in descending order rather than ascending now I can't just get away with trying to create this procedure again if it already exists if I try to execute this.
Procedure again now you will tell me that there is already an object called SP filmless in the database fortunately that's a really easy change to make to make sure this will work all I need to.
Do is make sure that the the word create is changed to the word alter instead if I exclude this procedure now that will modify and updates the way my store procedure works and if I finally head.
Back to the page that I had created earlier on and we execute this I should find that instead of ascending order my films now appear in descending order instead I suppose the next obvious.
Question is what if you want to modify a stored procedure and you don't still have the page which is created in the first place well just to show you that there's nothing to worry about so I'm.
Going to close down this page and I'm not going to take the changes but it's absolutely fine I don't need that page in the first place to modify the procedure once it exists and I can.
Access it through my object Explorer I want to make any changes to it I can simply right-click and choose to modify it and that will open up a new query window with all the code that I use to.
Create the procedure in the first place with a word alter and a couple of other bits and pieces as well we don't really need in fact all these extra bits and pieces are completely irrelevant to what.
We're doing and I'm going to delete those so let's see if I wanted to modify this again let's change it back to an ascending order and maybe we could include an extra field as well I see.
We've got film run time in minutes that's half the film Oscar wins as well nominations I've gone for it anyway that will do if I exclude this page again that will update my store procedure I.
