Optional parameters in sql server stored procedures Part 68

Text version of the video

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.

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

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
Insert into tblEmployee values
Insert into tblEmployee values
Insert into tblEmployee values

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

[abdullahmohammad]

very smart presentation of the lessons, Thanks man!

[kristinesmythe]

Fantastic teaching method. Finally as series that actually wants students to learn. Valuable and much appreciated resource.

[sams]

Thank you very much for your help... as it seem to me you are like the star so far, but all the people see your shine..

[Csharp-video-tutorialsBlogspot]

Hi Shanker, sure, will record and upload as soon as I can.

[aaronaaronaaron]

Venkar, sir, your chanel is so great. I have said thanks thousand times but one more is ok: Thanks!!!!

[shivtripathi]

Excellent Material provided by pragim technologies or venkat that is very useful for beginners and experienced person who wants to learn sql server 

[ymtan]

Thank you Venkat for such a great web database application video tutorial

[lhrintl]

Sir you are great. This is obviously an excellent video. Searching for this since long. Thanks.

[dhaneswarib]

Hi Venkat,
Your videos are just awesome!! Those are very clear and helpful :) Thanks alot for sharing!

[dtsguna]

Dear Venkat I Learned Lot from your videos.SQL Tutorials also brilliant

[narband]

wonderful video.... looking fro more videos in SQL Server....
Thanks much appreciated

[georgigeorgiev]

F Arial, Georgia is the best!

Awesome work as always, much better actually, because of the thorough example!

[wasimbader]

BIG THANK YOU ON REPLYING, WHAT I HAVE DONE IS 1, DOWNLOADED SQL EBOOKS, SQL AND EXCEL DATA ANALYSIS BY GORDON LINOFF. VERY GOOD BOOK AND EXACTLY THE WAY YOU HAVE TAUGHT US, FOLLOWS THE SAME PATTERN. IN MY OPINION ONE MUST LISTEN AND GO THROUGH YOUR TUTORIALS AND ALSO GO THROUGH THE BOOK BY GORDON LINOFF DATA ANALYSIS WITH SQL AND EXCEL. THANK YOU VENKAT SIR.

[jagdishjanganawar]

Thank u venkat for all this good stuff ! for begineers keep it and very helpful for us

[mariabufnea]

Your video are awesome...Thank you very much!!!

[Csharp-video-tutorialsBlogspot]

In a column of type nvarchar, you should be able to store the data you mentioned. Can you please share your query. May be after I have a look at it, I will be able to tell you, why it's not working.

[saagarsoni]

thnx a lot sir for positing video tutorials on SQL server

[Tarikffm]

Hello Venkat, I really hope more videos come from you! Your method is incredible! Thank you. Now today i have finish all your SQL Server Videos. Please Load more Videos e.g. for SSIS, SSAS, SSRS. And DDL Trigger or Execution Plan how we can read it to understand it! Or an example how we can Debbug a Stored Procedure! Your Tutorial is awesome. It will help a lot of People to find a job! Thank you kudvenkat Best regards from Germany!

[naodagere]

Thank you Venkat. I have used this code few yrs back and was a bit confused

[sunilkumarkashyap]

Hi, Venkat!
We are learning most from your posts!

could you please share the more about the sql.

it would be great full..for us!!

Thanks!!!