How to use optional parameter in SQL server SP

Please visit my new Web Site WWW.Codedisplay.com

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:

ALTER procedure Optional_Procedur 
@Name varchar(200)=null,
@Age int=null
As
BEGIN
if @Name is not null  
 print 'Your Name Is '+@Name
if @Age is not null
 print 'Your Age '+Convert(varchar(3),@Age)
END

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

exec Optional_Procedur 'Shawpnendu'
print '-----------------------------'
exec Optional_Procedur 'Shawpnendu',32
print '-----------------------------'
exec Optional_Procedur @Name='Shawpnendu'
print '-----------------------------'
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 regard.