Please visit my new Web Site https://coderstechzone.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.
0 comments:
I WOULD BE DELIGHTED TO HEAR FROM YOU