Using input and output parameters

Now I'd like to talk about sending input parameters and returning output parameters from a stored procedure. These are optional. We have worked with stored procedures thus far without working with parameters, so they are optional. I have some code on the screen. It's available in our exercise files. It basically alters the stored procedure and then executes the stored procedure. So I am going to alter the stored procedure to include a 1 input parameter for now. I do that by creating some parentheses.

We will need to put all of our parameters in parentheses. Then I need to give the parameter a name. And remember, in SQL Server, variable names always start with the at sign, and beyond that, you can name it almost anything you would like to name it. We will also need to declare what datatype we're going to use. Any of the typical SQL Server datatypes are in play, so for example, integer, varchar, datetime, all of those are perfectly valid. So I will declare this one as an integer.

So we're going to alter this procedure to now accept one parameter. That parameter is called @param1. We can reference that parameter throughout our code, and I will also alter our stored procedure to select parameter 1. So rather than select some arbitrary text, it's going to select that parameter that we passed to it. The code on top is now ready to go. It will successfully alter the stored procedure. The code on the bottom will need to be modified to pass it a parameter. The stored procedure is now expecting a parameter.

So we had better pass it a parameter and that parameter had better be an integer. So I will just pass it the integer 12. We'll run that. And it behaves as expected. Our code passed the parameter 12 to the stored procedure, and that exact value was returned to us. No surprises there. We'll call this a success. A few little notes on the syntax, when you are passing the parameter, you should not use parentheses. Although your instincts may tell you to use parentheses, you will in fact get an error if you use the parentheses.

So no parentheses when passing parameters. And also like I mentioned, it has to in fact be an integer. If I try to pass it some text, the machine will also get upset about that. It says error converting from varchar to integer and that is expected. So that's the basics of an input parameter. We could do multiple input parameters. I will come back up here to my parameter list, and put a single comma, and then I can list a second parameter. And again, it will need a datatype, and again, I will have to come down here, and make sure I am passing a second parameter to it, and that runs successfully.

We haven't actually done anything with the second parameter, so this becomes mildly more interesting, if we go ahead and display both parameter 1 and parameter 2. So no surprises there. We passed it the value 12 and Hello. It returned the value 12 and Hello. This is good. So now we've successfully worked with input parameters and we want to talk a little bit about output parameters. So we want to take this value that's being passed to it. Parameter 1 has a value of 12 and we're going to modify that value and then read the value as it comes out.

In order to do this, I will need to change a little bit of the code around the execute statement. Rather than just pass it the value 12, I want to pass it a variable. I will declare a new variable, just call it X, and we'll have to say that it is an integer, and I will go ahead and give it the value. We will then pass that variable to the stored procedure, and then we will read the value of that variable. And if all goes well, once we're done with this, it will be changing the value of that variable.

We haven't written the code for that just yet. But we can see it successfully passes the value of 13, and then reads the value of 13, as we are now, it is expected. So let's go ahead and try to change the value of the parameter. And here, we can say SET @param1 equal to some value other than 13; I will just choose a random number, 27. So what's going on here is lines 11, 12, and 13, should pass the value 13 to the stored procedure, the stored procedure should change that to 27, and then we can read that value.

This is not going to work just yet though. There is one more step that is a little counterintuitive to some people. We have to actually declare the parameter to be an output parameter. SQL Server assumes all parameters are input only. So if you would like them to be output, you have to specifically use the keyword OUTPUT in line 1 where we declare the stored procedure, we have to specifically say OUTPUT. And then again, on line 12 where we are passing the parameter, we also have to say specifically OUTPUT.

Now we should be able to successfully return the value 27. That is in fact what's happening. We've had success. So again, parameters are assumed to be input values. If you would like them to be both input and output, you have to use the keyword OUTPUT, and you have to use that keyword twice. We have to use output once when you declare the stored procedure, and once where we execute the stored procedure.