Optional Parameters in SQL Server Stored Procedure

This SQL Server tutorial will illustrate how to create and use the optional parameters in a SQL Server Stored Procedure.

When we are not sure if we need to insert data into a particular column or not, we use the concept of optional parameters in a stored procedure in SQL Server.

Use of Optional Parameters in SQL Server Stored Procedure

It is common to not be sure what data will be inserted into a column when using an optional parameter in SQL Server Stored Procedures.

Alternatively, if the user didn’t send data from that column into the store procedure, what will the store procedure do if the column isn’t a primary key or composite key? The first thing that happens is that it gives an error, as in the following example.

Let’s understand the problem that occurred step by step.

STEP 1: We have already created the EMPLOYEE_ACCESS table. To insert a new record into the table, we will create a stored procedure named “sp_insertnewrecord” with some necessary parameters.

CREATE PROCEDURE SP_INSERTEDNEWRECORD
(
@id int, 
@user_name varchar(20),
@department varchar(20),
@logintime datetime,
@loginexist datetime)
as
begin
INSERT INTO EMPLOYEE_ACCESS VALUES(@id,@user_name,@department,
@logintime,@loginexist)
END;

STEP 2: In the real scenario, sometimes the employee forgets to put the logintime and loginexist as well.

EXEC SP_INSERTEDNEWRECORD 21,'GREY89','Engineering';

Following the execution of the store procedure, an error message appears.

Msg 201, Level 16, State 4, Procedure SP_INSERTEDNEWRECORD, Line 
0 [Batch Start Line 16]
Procedure or function 'SP_INSERTEDNEWRECORD' 
expects parameter '@logintime', which was not supplied.

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 as follows:

if(LoginTime != ' ')
-- now sent actual value 
else (
-- sent desire value
if(LoginExist != ' ' )
-- here also sent actual value 
else (
-- sent desired value

So, the ‘else’ condition is completely extra in this code. And to get rid of that code, we will use optional parameters in the stored procedure. To set the default value in the input parameter of the stored procedure which is not mandatory, we have used = NULL as the default value.

CREATE PROCEDURE SP_INSERTEDNEWRECORD
(
@id int, 
@user_name varchar(20),
@department varchar(20),
@logintime datetime=NULL,
@loginexist datetime=NULL)
as
begin
INSERT INTO EMPLOYEE_ACCESS VALUES(@id,@user_name,@department,
@logintime,@loginexist)
END;

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

EXEC SP_INSERTEDNEWRECORD 22,'GREY89','Engineering';

Now it runs successfully, and checks the output:

SELECT * FROM EMPLOYEE_ACCESS
WHERE ACCESS_ID=22;

With the help of the SELECT statement, we retrieve all records from the EMPLOYEE_ACCESS table by the WHERE condition. In the WHERE condition, we check the access id equal to 22. This will bring the records of the employee’s access whose id is 22.

ACCESS_IDUSER_NAMEDEPARTMENTLOGIN_TIMELOGIN_EXIST
22GREY89EngineeringNULLNULL
A figure of Employee_Access Table with 3 parameters.

Also, check: SQL Server stored procedure naming convention & best practices

Now we will try to insert actual data with all 5 records into the EMPLOYEE_ACCESS table but no default values.

EXEC SP_INSERTEDNEWRECORD 23,'Jwick004','Engineering','2022-04-01','2022-04-01';

After the execution of the stored procedure, we will execute the above parameters in the stored procedure. Select the output as the following:

SELECT * FROM EMPLOYEE_ACCESS;

Now, the stored procedure execution has been done with 5 parameters. Then the SELECT statement will retrieve all records from the EMPLOYEE_ACCESS table.

ACCESS_ID USER_NAMEDEPARTMENTLOGIN_TIME LOGIN_EXIST
11wraddenburyaSupport26-02-202226-02-2022
12drudyardbBusiness Development10-09-202210-09-2022
13kjohncTraining16-11-202216-11-2022
14byakunkindSupport18-01-202218-01-2022
15uciccottoeProduct Management30-03-202230-03-2022
16lofallonfBusiness Development22-06-202220-06-2022
17agonthardgEngineering18-03-202218-03-2022
18bblinermanhEngineering17-05-202217-05-2022
19jkleinhandleriEngineering29-11-202229-11-2022
20mgerreyjMarketing24-11-202224-11-2022
22GREY89EngineeringNULLNULL
23Jwick004Engineering01-04-202201-04-2022
Image of EMPLOYEE_ACCESS table after execution of optional parameter in store procedure

We hope you understand how to use the SQL Server Optional Parameter in Stored Procedure in SQL Server.

Conclusion

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

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