# SQL Tutorial - Stored Procedures Optional Parameters ## Метаданные - **Канал:** BeardedDev - **YouTube:** https://www.youtube.com/watch?v=aPrrKXyGVAM ## Содержание ### [0:00](https://www.youtube.com/watch?v=aPrrKXyGVAM) Segment 1 (00:00 - 05:00) hello and welcome to my channel I am BD Dev and in this video we're going to be taking a look at optional parameters with installed procedures as always if you would like to follow along the code is available in the description and we're just going to be working through a very simple example today so I'm just going to be creating a customers table and what we need to pay attention to here is a couple of columns middle names which is nullable and home address line two which is nullable as well I have also created a very simple straw procedure called SP insert customers which is just going to be inserting some values that we pass in as parameters so at the moment we've got the first name the last name the date of birth home address line one home address City and home address postcode going back to the table we also have some default constraints added to set the created date and modified dates within this table to the current timestamp now the problem we're facing is because middle names and home address line two is optional not everybody has middle names and I really has a home address line two or because or we might not retrieve that information from a customer so one approach we have with creating a simple stored procedure like this is simply to ignore those values let's have a look at executing this store procedure first of all and have a look at what that looks like so I've made up some values here and if I just go ahead and execute that store procedure and have a look at the final results that's inserted okay but the problem we have with this approach is what if a customer does give us a middle name or a home address line too we would actually like to write that information to the database now an obvious solution to this problem is we actually add those additional columns as parameters within the store procedure as you can see here I'm just modifying the store procedure to add in those values and I'm also going to change the insert statement as well to insert the results of those so we're adding middle names to insert list just add the comment and then also home address line two and I come after that and then we'll set as middle names I'll just copy the line above full speed I'll change that to home address line two if we execute that statement we can update our stored procedure and then simply what we could do is just pass in middle names equals null and home address line two no and of course if it wasn't no then we'd uh we'd change that as well so I'm just going to execute this again it's going to insert exactly the same customer but we'll see it has a different created date so again if we have a look at the table typically we wouldn't do this but we just test in here so we can see a new record's been inserted exactly the same as before now this approach has two problems the first is if we're calling this procedure in multiple locations we're going to need to duplicate this effort that's not a huge problem just a little niggle a little inconvenience it's not going to cause any problems the other problem is if we're having a large amount of nullable columns if we added telephone numbers email addresses work details there's a lot of information we could potentially retrieve about a customer and if we added all of those attributes or columns to this table then we could end up with tens of parameters that have null values so a better approach is to use optional parameters an optional parameters are really a byproduct of adding default ### [5:00](https://www.youtube.com/watch?v=aPrrKXyGVAM&t=300s) Segment 2 (05:00 - 09:00) values to our stored procedure the problem with adding these um nullable parameter values these are actually we could be transferring a lot more traffic over the network than needed which wouldn't be a problem if we're only running this stored procedure once or twice but if we're inserting data every time a customer makes an inquiry or an order there's going to be a lot of unnecessary traffic flowing over the network so let's have a look at how we can change this store procedure and add some default values so again I've returned to the definition of our stored procedure and to add a default value what we can do within the parameter um definition here with well within the Declaration of our parameters within the storm procedure we can simply add the word equals null and it doesn't have to be normal you're only limited by the data types it's just that Norm makes sense in this scenario and again we're going to do that for home address line two and again we can leave those now within the insert statement because they're still going to be part of our insert but what that allows us to change and I'll just execute that to update that store procedure but what that means is now we no longer need to pass in these parameter values when executing this stored procedure so if I now remove middle names and home address line two I can now execute that stored procedure without passing in those values and again we'll see a new instance of that customer now if we did want to add in the middle name or a home address line two let's just add in a middle name just to test this will be picked up let's just add in the middle name of bird and if I execute this store procedure have a look at our customers table we can see that that's actually been inserted now if we go back and remove these default parameters that actually make them optional and we'll just execute to Upstate that stored procedure now if I wasn't to enter one of those values at this point I would get an error to say this parameter is expected so without assigning those default values we don't have optional parameters now another important point is if you are working with Azure synapse Analytics optional parameters are not supported at the moment I had a look the other day at a piece of work I was working on wanted to add in some optional parameters and found out they weren't supported so I had to find an alternative way but again if we want to add optional values we could set this middle name to anything that is limited by the string so let's add middle name of Paul let's leave this as solid home so again we can execute that now if we execute it with our setting of burst we're going to overwrite that default value or that's what's going to be inserted so if I execute that we will see the middle name has been inserted as Birds we didn't overwrite the home address line 2 so that has been inserted as the default value if I was to remove the middle name from the execution of the store procedure and execute that again and have a look at those results and I can see Paul has been chosen as the middle name so the only limitations here are by your data type definitions really hope you have enjoyed that video if you have let me know in the comments below and if you'd like to see any other videos on data engineering or data analysis please do let me know thanks a lot for watching --- *Источник: https://ekstraktznaniy.ru/video/44477*