Optional Parameters in SQL Server Stored Procedure

Generally, when filling out Online forms, we don’t know what to fill in the particular columns. For example, the Email field will not be mandatory (which is an optional parameter)every time, so in the front end, the user skips it. That column will be taken as Null or not applicable in the system.

Here, we will see how to create and use the optional parameters in a SQL Server Stored Procedure.

Use of Optional Parameters in SQL Server Stored Procedure

This feature is particularly useful when dealing with complex queries where some parameters may not always be necessary.

Step 1: Here, I am creating the table called StudentInfo.

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

Step 2: I created the stored procedure for the table StudentInfo.

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

Step 3: Now, it will throw an error while executing. Because students might not have EmailID, they will skip this.

Normally, a developer like us wants to skip the error for the solution. To set default or null value, we need to write some conditional code that will increase some lines of code.

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

The ‘Else’ condition is not required here. To avoid the code, we will use optional parameters in the stored procedure. To set the default value, we have used N/A. We can also use NULL.

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

Now, let’s try to execute the stored procedure with 3 parameters.

Exec sp_InsertRecord 10,'James','2012-08-04', 8040569876

Now it runs successfully and checks the output:

select * from StudentInfo

With the help of the SELECT statement, we retrieve records from the StudentInfo table.

Conclusion

In this article, we have learned about the optional parameters in the store procedure in SQL Server and covered a step-by-step example of using optional parameters in SQL Server.

You may also like to read the following SQL Server tutorials.

• SQL Server stored procedure modified date
• SQL Server stored procedure parameters
• SQL Server stored procedure return value
• How To Update Table Using JOIN in SQL Server