How to use optional parameter in SQL server SP

When you are going to write a generic SP for any business purpose you may realize the necessity of optional parameter. Yes Sql server gives us the opportunity to use optional parameter in SP arguments. You may write a SP with 3 arguments but based on your business rule you may pass one or two or three valuse as you want. This policy not only ease our life but also help us to write short SP. Here in this article i will discuss how one can create a optional list SP & execute thie SP or stored procedure.

Ok first write a SP with two optional field like below:

view sourceprint?
01 ALTER procedure Optional_Procedur

02 @Name varchar(200)=null,

03 @Age int=null

04 As

05 BEGIN

06 if @Name is not null

07 print 'Your Name Is '+@Name

08 if @Age is not null

09 print 'Your Age '+Convert(varchar(3),@Age)

10 END

Now you can call the SP in many different ways like:

view sourceprint?
1 exec Optional_Procedur 'Shawpnendu'

2 print '-----------------------------'

3 exec Optional_Procedur 'Shawpnendu',32

4 print '-----------------------------'

5 exec Optional_Procedur @Name='Shawpnendu'

6 print '-----------------------------'

7 exec Optional_Procedur @Age=32


The query output is given below:

Your Name Is Shawpnendu
-----------------------------
Your Name Is Shawpnendu
Your Age 32
-----------------------------
Your Name Is Shawpnendu
-----------------------------
Your Age 32

I.E. You can send parameter specific values or sequential values or you are not bound to send parameter values in this.