And add them to your stored procedures we'll talk about how you execute store procedures which include parameters and how you can pass values back to them and we'll move on then and show you how you.
Can work with optional parameters and provide default values we'll finish this tutorial with a quick look at how you can then use your store procedures in other applications such as.
Sequel server reporting services so let's get started in a previous tutorial video we've shown you how you can create basic store procedures we simply encapsulate a basic select statement.
When you execute a type of store procedure is simply as though you'd run the simple select statement in the first place in this video we're going to expand on that idea and allow you to.
More bits of information in fact that will affect the results returned if I change the value in this simple single parameter and execute the procedure again I'll get a completely different.
Set of results so that's the idea for our first example we're going to write the store procedure which accepts a single parameter when we execute it and the data type is going to be some kind.
Of number and the number that we pass in each time we execute the procedure is going to be used to filter our list of films based on the running time in minutes so let's go about converting.
This simple select statement into a create procedure statement I'm going to write create proc about the Select statement I'm going to call SP filming criteria and then below that the word as.
And that's actually all that I really need to do at this point just a quick reminder if you watched our earlier video on basic store procedures that you can also optionally add a big.
Posts Related:
And end block to your procedure creating
Statements it's not necessary in this example simply because our store procedure only consists of a single statement there it is.But in future when you're writing more complex procedures involving many many statements the beginning the end block is useful so it's worth are getting into the habit early so now that we have the.
Basic procedure outline the next step is to start adding a parameter to it now the list of parameters always appears in a set of round brackets or parentheses immediately after the procedures name.
So after ASP film criteria you can simply open a set of round brackets and then begin to list your parameters now what parameter names must begin with an @ symbol so I'm going to write an @.
Symbol and then think of sensible name for my parameter I'm going to call it min length I think oh yeah min length oddly I also have to specify what kind of data that parameter will be allowed.
To contain so I can do that with him as you word min length as in this case I'm going to use the add the data type of int which is short for integer which any whole number I can then close around.
Brackets and that's my list of parameters created in a couple of later examples we'll add further parameters to this list so you can see how you work with multiple parameters now there's not.
Much point in creating a parameter or unless we're actually going to use it so we're going to do now is add a where clause to our select statement and we're going to use that parameters value in.
The criterion so I'm going to ask why the films of running time in minutes is greater than whatever value has been passed in through this parameter all I need to do now is execute this script to.
Create the store procedure and if I do
That there is commands completed successfully and I should be able to have a look for the procedure in my object Explorer if I expand the.Databases folder and expand the movies database and the programmability folder and store procedures and there it is SP film criteria so now that we've created the procedure we need.
To execute it in order to see his results now because I've added a parameter to this procedure I can't use the old trick of simply selecting his name in the script that I used to create.
It and choosing to execute it I'll be provided with a message saying that it's missing an expected parameter min length so what I need to do instead is create a new query window and write the exec or.
Execute statement exec SP film criteria which is the name of my store procedure and again if I try to execute it at this point then I be informed with the same message I've not provided a parameter.
That is expected so what I need to do is provide a value for the min length parameter which it must be a number and type in the value of 180 and if I execute that procedure now I'll get a.
List of all of the films each one time in minutes is greater than the value that I provided if I modify this piece of information here and execute the query again I get a different set of.
Results and that will happen every single time I run the store procedure so now we've seen the basics of creating parameters in the stored procedure let's go back and add a couple more if you.
Still got the script open which you use to create this procedure in the first place you can happily come back to this one alternatively if you'd close that one.
Down and hadn't save the changes don't and urn you can have similar back to the air the object Explorer right-click on the name of your stored procedure and you can modify it and that'll open up.
The add the page game with the alter statement rather than the create statement I'm going to get rid of these extra lines that we don't need to consider in our scripts and there we go.
Alter procedure SP film criteria and we're ready to go quick reminder of that the red underlining that you'll often see when you're using the intellisense feature it doesn't refresh automatically.
Immediately when you create new procedures so the moment this film criteria procedure isn't actually recognized to force the intellisense to to recognize that keyword you know to.
The Intelli sorry the Edit menu choosing tele cents and then choose to refresh the local cache or just simply press ctrl + Shift + R on your keyboard and that showed up for a brief pause clear.
The red underlining it's not a big issue just if it annoys you can simply clear it by doing that so now we're ready to start adding new parameters to our current procedure and do that all I.
Really need to do is type a comma after the first parameter and then make up a new name for the next parameter so simply build a comma separated list much like the Select list when you're when.
You're listing the field names or column names now when I'm doing this I like to create a slightly more easy to read layout what I'm going to do is take the parameter list onto a different line.
Versatile and indent that by pressing the tab key I'm also going to take the list of parameters onto individual separate lines as well so leave the brackets or the parentheses by.
Themselves on their own separate lines and then what we'll end up with is I mean very similar to the way that I structure my select lists you have one parameter on one line and on the next.
Line beginning with a comma the name of the next parameter the next parameter we're going to create is going to be called at max length and it's going to have the same data type as the.
Previous one so it's going to be an integer what I'm going to do then is add a new section to my my where clause in my Select list my select statement sorry I'm going to add a little little.
Operator there as well and that's why the film run time in minutes is greater than or equal to the minimum length I've provided and film run time minutes is less than or equal to the max length.
I've provided all I need to do now is execute this page to alter my procedure and if I do that hopefully I'll get the commands completed successfully message I can then head back to the test.
Procedure if you like if I try to run this one again now it's not going to work it's going to give me the same message as I saw earlier on is expecting a parameter which was not supplied so.
All I need to do is type in a comma after my first parameter add a new value a new integer and when I execute the procedure now I've got a list of all the films it's run time is between 150 and.
180 I just want to make a quick note about naming your parameters when you're providing multiple bits of information at the same store procedure sometimes it's not very obvious what those numbers.
Are meant to represent so if you know the names of your parameters you can actually name them when you call the stored procedure in fact in this version of sequel server 2012 the tooltip will.
Pop up to show you the the list of parameters that you've created and what data types they're supposed to contain so I just carry on here I'm going to say at min length equals 150 and then I can.
Say also at max length equals 180 so you simply name the parameter and before you provide its value it doesn't affect the way the procedure works it should make things a little bit more obvious for.