Optional Parameters in Stored Procedure in SQL

The idea of optional parameters in a Stored Procedure in SQL is that you are often not sure whether or not the data will be inserted into a specific column, in other words the column that is not a primary key or not using a composite key and you want that if the user didn't send any data for that column into the Stored Procedure then what will Stored Procedure do? Yes it will give an error, just like the problem that occurs in the following example.

Step 1: I have created a table named "Person" with some fields.

                                                               figure-1.1

 

Step 2: Now I have created a Stored Procedure named "sp_InsertRecord" with some mandatory parameters to insert the record in the table.

                                                                  figure-1.2

 

But in the real scenario sometimes the user doesn't have "ContactNumber" and "EmailID" as well.

exec sp_InsertRecord 1,'xyz','2014-02-20'

Note: In this execution I have assumed that the user didn't send "ContactNumber" and "EmailID".

After the execution of the procedure it will give an error as in the following:



                                                                           figure-1.3

 

Question: So what will the developer do to skip this error, is there any solution?

Answer: Of course the developer can avoid this kind of problem, like send the null value or default value via code if "ContactNumber" and "EmailID" are empty, but you need to write some conditional code yhat will increase some lines of code as in the following:

                                                                        figure-1.4

 

Origin of Optional Parameter

As we see in the code above, the "else" section is completely extra in the code, so to get rid of this extra code we use optional parameters in a Stored Procedure.

Set the default value in the input parameters of the Stored Procedure that are not mandatory like I have used ='N/A' as the default value.

                                                                                  figure-1.5 

 

Now execute the Stored Procedure with only 3 input parameters.


                                                                                       figure-1.6

 

It runs successfully; now check the output by:

select * from person

                                                 figure-1.7

 

And if we insert the actual data with all 5 input parameters then the actual data will be inserted, not the default values.

exec sp_InsertRecord 2,'ABC','2014-02-20','1234567890','[email protected]'

After the execution of the stored procedure above with all input parameters, select the output as in the following:

select * from person


                                                figure-1.8