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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.