Optional Parameters in Stored Procedure in SQL

Introduction

In this article, we learned about Optional Parameters in Stored Procedures in SQL.

Optional Parameters in Stored Procedures 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.

Create Table Person
(
ID int,
Name Varchar (100) ,
DateofBitrh date,
ContactNumber Varchar (10) ,
EmailID Varchar (50)
)

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.

Create Procedure [dbo].[sp_InsertRecord]
(
@ID int ,
@Name Varchar (100) ,
@DateofBitrh date,
@ContactNumber Varchar (10),
@EmailID Varchar (50)
)
as
begin
Insert into Person values (@ID, @Name, @DateofBitrh,@ContactNumber, @EmailID)
end

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

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

Of course, the developer can avoid this kind of problem, like sending 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.

if(ContactNumber != "")
{
  --Send Actual Value
}
else
{
  --Send Desired Value
}

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.

Alter Procedure [dbo].[sp_InsertRecord]
(
@ID int ,
@Name Varchar (100) ,
@DateofBirth date,
@ContactNumber Varchar (10) = 'N/A',
@EmailID Varchar (50)='N/A'
)
as
begin
Insert into Person values (@ID,@Name,@DateofBirth,@ContactNumber,@EmailID)
end

figure-1.5

Now execute the Stored Procedure with only 3 input parameters.

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

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

Conclusion