How to use optional parameter in SQL server Stored Procedure (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 as Stored Procedure (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 your need. This policy not only ease our life but also help us to write efficient SP/Stored procedure. Here in this article I will discuss how one can create a optional list Stored Procedure (SP) arguments & use those by executing the SP or Stored Procedure.

First write a Stored Procedure (SP) with two optional parameter 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 call the Stored Procedure (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 even you are not bound to send any parameter value to the Stored Procedure.

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

     

*