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_ID	USER_NAME	DEPARTMENT	LOGIN_TIME	LOGIN_EXIST
22	GREY89	Engineering	NULL	NULL

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_NAME	DEPARTMENT	LOGIN_TIME	LOGIN_EXIST
11	wraddenburya	Support	26-02-2022	26-02-2022
12	drudyardb	Business Development	10-09-2022	10-09-2022
13	kjohnc	Training	16-11-2022	16-11-2022
14	byakunkind	Support	18-01-2022	18-01-2022
15	uciccottoe	Product Management	30-03-2022	30-03-2022
16	lofallonf	Business Development	22-06-2022	20-06-2022
17	agonthardg	Engineering	18-03-2022	18-03-2022
18	bblinermanh	Engineering	17-05-2022	17-05-2022
19	jkleinhandleri	Engineering	29-11-2022	29-11-2022
20	mgerreyj	Marketing	24-11-2022	24-11-2022
22	GREY89	Engineering	NULL	NULL
23	Jwick004	Engineering	01-04-2022	01-04-2022

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.

• 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