This article will teach you about optional parameters and how to use them in stored procedures.

Parameters that are optional
Optional parameters are a sort of parameter in which you can declare a default value, and if that parameter is not passed, the default value is used.
By using several examples, let's establish a "Employees" database and learn how to write a stored method with Optional Parameters.

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, 'Peter', 'London', NULL, 65000),
(102, 'Scott', 'Manchester', 1234567809, 55000),
(103, 'Laura', 'Leeds', NULL, 95000),
(104, 'Samuel', 'Liverpool', 1234506789, 35000)

Now check the table by running a select query.
SELECT * FROM Employees;

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
Now let’s execute the same procedure and see the output.

EXECUTE usp_InsertEmployeeRecords 105, 'Maria', 'Leeds', 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 = 'Ricky', @EMPAddress = 'London', @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.

HostForLIFEASP.NET SQL Server 2022 Hosting