Optional parameters in sql server stored procedures Part 68

Text version of the video
http://csharp-video-tutorials.blogspot.com/2013/09/part-68-optional-parameters-in-sql.html

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

Slides
http://csharp-video-tutorials.blogspot.com/2013/09/part-68-optional-parameters-in-sql_16.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

Parameters of a sql server stored procedure can be made optional by specifying default values.

We wil be using table tblEmployee for this Demo.
CREATE TABLE tblEmployee
(
Id int IDENTITY PRIMARY KEY,
Name nvarchar(50),
Email nvarchar(50),
Age int,
Gender nvarchar(50),
HireDate date,
)

Insert into tblEmployee values
(‘Sara Nan’,’Sara.Nan@test.com’,35,’Female’,’1999-04-04′)
Insert into tblEmployee values
(‘James Histo’,’James.Histo@test.com’,33,’Male’,’2008-07-13′)
Insert into tblEmployee values
(‘Mary Jane’,’Mary.Jane@test.com’,28,’Female’,’2005-11-11′)
Insert into tblEmployee values
(‘Paul Sensit’,’Paul.Sensit@test.com’,29,’Male’,’2007-10-23′)

Name, Email, Age and Gender parameters of spSearchEmployees stored procedure are optional. Notice that, we have set defaults for all the parameters, and in the “WHERE” clause we are checking if the respective parameter IS NULL.
Create Proc spSearchEmployees
@Name nvarchar(50) = NULL,
@Email nvarchar(50) = NULL,
@Age int = NULL,
@Gender nvarchar(50) = NULL
as
Begin
Select * from tblEmployee where
(Name = @Name OR @Name IS NULL) AND
(Email = @Email OR @Email IS NULL) AND
(Age = @Age OR @Age IS NULL) AND
(Gender = @Gender OR @Gender IS NULL)
End

Testing the stored procedure
1. Execute spSearchEmployees – This command will return all the rows
2. Execute spSearchEmployees @Gender = ‘Male’ – Retruns only Male employees
3. Execute spSearchEmployees @Gender = ‘Male’, @Age = 29 – Retruns Male employees whose age is 29