Stored procedure input parameters - SQL Server Tutorial

- [Instructor] Like functions, stored procedures can include input parameters to alter the actions that these stored procedure performs. Unlike functions and views though, stored procedures can write data to our existing tables, rather than just returning existing values. Let's take a look at both capabilities by exploring the data and adding rows to the Wideworldimporters warehouse.colors table. Let's first take a look at its current content. By selecting everything from Warehouse.Colors, and I'm going to order by the color ID, descending. This table has five columns. But to add a new row, we only need to supply values for the first three. The valid from and valid to columns, are automatically populated by SQL server since this is a system version or a temporal table that keeps a record for when rows are added and modified. So let's focus on the first three columns. The color ID is the primary key for the table. Integer values here are all one larger than the previous row. Notice that the highest value right now is 36. This will be important in a moment. The color name column has a unique index on it so that names can't be repeated. And the last edited by column links to the application.people table and is meant to keep track of who added each color. Now it looks like all of these rows were input by the same person, the person with the ID number one. So we're going to use this number for all of the rows that we'll add in this example. Now, normally you need to perform an insert command and name each of the columns in order, in order to add a row to the table. If you're trying to keep end users from interacting directly with the databases structure, then that becomes a problem. Since they need to know the column names in order to successfully perform the insert. By wrapping the commands inside a base store procedure, we can make the process easier and more secure. So let's create a stored procedure that'll allow an end user to input a new row into this table. I start that on line number 11. We're going to create or alter a procedure in the warehouse schema called uspInsertColor. Now just like functions, we can add input parameters that the end user will use to pass values to the store procedure. Inside the parenthesis, you'll name each variable and give it a data type. Here, I'm going to add an input parameter called @Color and it'll store an end var char 100 value. Then we have the, AS keyword , followed by the things that we want the procedure to do. To describe what's happening here, let's look at the routine backwards. Ultimately, we want to insert a row into the colors table. Then we can see that on lines 15 and 16. We'll need to supply the three values for color ID, colored name, and last edited by. The last edited by value is easy. We're just going to use the number one for every row that we insert. So this one is done already. The color name will come directly from the parameter that the user supplies, when they run the procedure, we named it @Color up here. So we'll just place that down here into the value statement. That just leaves the color ID value to work with. This one's a little bit trickier. We need to get the color ID to be one larger than the existing highest number in the table. To get this all, first declare a new variable called @Color ID on this line, by number 13, it'll store an integer value. Then on line 14, we'll set its value. We'll do that by selecting the current highest color ID from Warehouse.Colors. To do that, we'll use the max function applied to the color ID column, then whatever number, this returns, we're going to add one, to it. Finally, we'll take this result and we'll store it in the @Color ID variable, and finally use it down here in the values clause. So that finishes the steps to insert a new row. We can have the store procedure go one step further and show us the result of our insert as a second operation in the same procedure. So after the insert command I have a select command where we're going to select everything or all of the columns from Warehouse.Colors, where the color ID is equal to the color ID that we just inserted. Also notice that since our procedure is performing two different and complete actions. Make sure that you include the semi-colon at the end of each one. So here we have a semi-colon, that completes the select clause. And here we have a semi-colon, that completes the insert statement. So let's go ahead and create the procedure. By highlighting lines 11 down to 21 and executing them. That will create the procedure and now we can test it out. We'll execute the store procedure,Warehouse.uspInsertColor, and then we need to pass in the parameter. In order to pass a parameter to a store procedure, you can either specifically identify the parameter's name. For instance, I have @Color equals periwinkle blue here, or, you can remove the variables name and supply the values in the same order that they were declared, when you first created the store procedure up here. Now we only have one variable in our store procedure called @Color. So I don't need to actually name it down here. I can just say, execute Warehouse.uspInsertColor periwinkle blue SQL server will understand that this is the variable color. So let's go ahead and execute line number 24. That performs the insert, and then also selects the data back out of the table. And I can see that we get the color ID 37 and the color name periwinkle blue has been added to the table. Now here is what's important about executing this stored procedure. Let's assume that we're performing this task, as an end user. We don't need to know anything about the database in order to successfully add the color. We don't need to know the table's name. We don't need to know the column name, where the value is ultimately getting stored. We don't need to know anything about the primary key column for the table. We don't need to know any other columns exist in the table, and we don't need to know anything about the structure of this database, and yet we're still able to add the new row. All we need to do is execute these store procedure and pass it the color name, that we want added. If we put our developer hat back on again, we can provide another store procedure that removes the last row from the table. I've got that illustrated on line number 31, this is a pretty straightforward row delete statement starting on 33 and 34. But if there were other changes that need to be made in your database to accommodate the rows deletion, you could code all of that in the store procedure. So perhaps the deletion of the row causes other information in other tables to get updated, or the action needs to be logged, in an audit table or, the deletion triggers the website storefront, to refresh. Whatever complex actions that need to be performed, could be included in these store procedure. And none of that needs to be revealed to the end user. If I create this procedure, all I need to do is execute Warehouse.uspRemoveLastColor and the rows are moved from the table. To verify, it I can select everything from Warehouse.Colors and I'll see them back to the highest value of 36. So all the end user needs to do is execute the warehouse.uspRemoveLastColor stored procedure, and the stored procedure will handle all of the complex actions that need to be performed in order to accommodate that task.