Optional Parameters in SQL Server

Introduction

In this article, you will learn what is optional parameter is and how to use it in stored procedures.

Optional parameters

Optional parameters are a type of parameter where you can declare a default value, and if you don’t pass that parameter, the default value would be considered.

Let’s create an “Employees” table and understand how to create a stored procedure with Optional Parameters by taking some examples.

CREATE TABLE Employees (
EMPID INT PRIMARY KEY,
EMPName VARCHAR (50),
EMPAddress VARCHAR (Max),
ContactNumber VARCHAR (10),
EMPSalary INT
)

--Inserting multiple records in one table.

INSERT INTO Employees (EMPID, EMPName, EMPAddress, ContactNumber, EMPSalary)  
VALUES (101, 'Harsha', 'Hyderabad', NULL, 65000),
(102, 'Rohit', 'Pune', 1234567809, 55000),
(103, 'Karan', 'Mumbai', NULL, 95000),
(104, 'Faizal', 'Hyderabad', 1234506789, 35000)

Now check the table by running a select query.

SELECT * FROM Employees;

And “Employees” table looks like below.

Now create stored proc, which inserts records in the table with mandatory parameters.

CREATE OR ALTER PROCEDURE usp_InsertEmployeesRecords
@EMPID INT PRIMARY KEY,
@EMPName VARCHAR (50),
@EMPAddress VARCHAR (Max),
@ContactNumber VARCHAR (10),
@EMPSalary INT
AS 
BEGIN
	INSERT INTO Employees VALUES (@EMPID, @EMPName, @EMPAddress, @ContactNumber, @EMPSalary)
	SELECT * FROM Employees						--Checking if all the records inserted in Employee table or not.
END

The stored procedure “usp_InsertEmployeesRecords” was created, as you can see in the above screenshot. Now let’s execute the same procedure and see the output.

EXECUTE usp_InsertEmployeeRecords 105, 'Adil', 'Mumbai', 1203456789, 70000

As you can see above, all records are inserted in the table as values passed for all mandatory parameters.

Now let’s go with another scenario when a user doesn’t have all the information and inserts records in the table for example missing contact number.

Executing the same procedure again.

EXECUTE usp_InsertEmployeesRecords @EMPID = 106, @EMPName = 'Adam', @EMPAddress = 'Delhi', @EMPSalary = 80000;

After executing the procedure, we got the following error, which means we had created proc with mandatory parameters, and we have not passed the values for all the parameters.

Now to overcome with above error, let's create the proc with an optional parameter.

CREATE OR ALTER PROCEDURE usp_InsertEmployeesRecordsOptParam
@EMPID INT PRIMARY KEY,
@EMPName VARCHAR (50),
@EMPAddress VARCHAR (Max),
@ContactNumber VARCHAR (10) = 'NULL',
@EMPSalary INT
AS 
BEGIN
	INSERT INTO Employees VALUES (@EMPID, @EMPName, @EMPAddress, @ContactNumber, @EMPSalary)
	SELECT * FROM Employees						--Checking if all the records inserted in Employee table or not.
END

The stored procedure “usp_InsertEmployeesRecordsOptParam” was created, as you can see in the above screenshot. Now let’s execute the same procedure and see the output.

Executing procedure with passing only 4 input parameters

EXECUTE usp_InsertEmployeesRecordsOptParam @EMPID = 106, @EMPName = 'Adam', @EMPAddress = 'Delhi', @EMPSalary = 80000

You can see above that all records are inserted in the table even when we don’t pass the contact number. It happened because we made the contact number column optional.

Conclusion

Today we have learned what is optional parameters and when and how to use them. Optional parameters are useful when you don’t have all the parameters handy during calling. I hope you liked the article. Looking forward to your comments/suggestions.