Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

You have a stored procedure GetCustomers with two parameters: LastName, FirstName. The stored procedure returns all the records matching the values of the parameters. You want the parameters be optional, which means skipping the parameter if you do not pass a value.

T-SQL does not provide optional parameters, but you can implement one.

1.       You have original stored procedure

2.       Add =null at your parameter declaration of the stored procedure

3.       Add IS NULL at your WHERE clause

4.       Now you have optional parameters in the stored procedure

5.       Call the stored procedure from your data access layer C# code (FirstName is optional)

       

42 Comments

  • Nice article but you can make your SQL faster by using COALESCE in the where clause:

    WHERE LastName=COALESCE(@LastName,LastName) and FirstName = COALESCE(@FirstName,FirstName)

  • No, you can not. Sorry to say, but learn the basics before making comments.

    LastName = COALESCE (@LastName, LastName) should trigger a table scan because COALESCE (@LastName, LastName) must be evaluated for EVERY SINGLE LINE.

    WHERE (LastName = @LastName OR @LastName IS NULL)

    does not trigger this as you have EITHER a constant true on the right side (@LastName IS NULL) OR a constant false there and a simple expression that can utilize an index.

    Beginner mistake. Well, at least you have the guts to put that into a public comment, Paul.

    One thing seriously missing is the explanation of the HUGH downside unless one creates the stored procedure "with recompile" - which is that the FIRST run decides the execution plan, and that is being reused. If I pass in null = @LastName, soemthing in @FirstName on the first run, this decides the execution plan... also being used for something in @LastName, null in @FirstName.

    To avoid this, the SP must be recompiled on every execution. Especially with something like given in this example this may be significant bad performance otherwise.

  • Stanley,
    I think that the problem on this solution is because when you use the statement ([LASTNAME] = @LASTNAME OR @LASTNAME IS NULL) in the WHERE clause and the column LASTNAME has a INDEX the SqlServer does not use the INDEX to improve performance on the query!

  • Well this is OK but one more interesting way to do so is use it like

    WHERE LastName=ISNULL(@LastName,LastName)
    and FirstName=ISNULL(@FirstName,FirstName)

  • Nice comment amsneuton exactly what solved my issue. simple and succinct. Made my day!

  • Hey Thomas Tomiczek, you strike me as a guy who knows a lot about SQL Server, and who also happens to be a jerk. Congrats on the first one. Work on the second.

  • how to write sql query withoptional parameter

  • db1sry Looking forward to reading more. Great blog.Thanks Again.

  • thanx. it worked for me

  • MjAaiq Thanks a lot for the blog article.Much thanks again. Will read on...

  • CEVygu I value the post.Really looking forward to read more. Great.

  • My partner and I stumbled over here from a different page
    and thought I might check things out. I like what I see so now i'm following you. Look forward to checking out your web page again.

  • Have you ever considered about adding a little bit more than just your articles?
    I mean, what you say is fundamental and everything. However think of if you
    added some great pictures or video clips to
    give your posts more, "pop"! Your content is excellent but with images and
    videos, this site could certainly be one of the greatest in its field.
    Terrific blog!

  • Greate article. Keep posting such kind of info on your blog.
    Im really impressed by it.
    Hello there, You've performed a fantastic job. I'll certainly digg it and personally recommend to my friends.
    I am sure they'll be benefited from this web site.

  • Hello! Do you know if they make any plugins to assist with Search Engine Optimization?
    I'm trying to get my blog to rank for some targeted keywords but I'm not
    seeing very good gains. If you know of any please
    share. Cheers!

  • I have been surfing online more than 3 hours today, yet I never found
    any interesting article like yours. It is pretty worth enough for me.
    In my view, if all site owners and bloggers made good
    content as you did, the net will be much more useful than ever before.

  • You really make it seem really easy together with your presentation however I to find this topic to be really something that I think I would never understand.
    It seems too complex and extremely vast for me. I'm looking forward in your next put up, I'll
    try to get the grasp of it!

  • Please let me know if you're looking for a author for your weblog. You have some really great articles and I think I would be a good asset. If you ever want to take some of the load off, I'd
    love to write some material for your blog in exchange for
    a link back to mine. Please send me an e-mail if interested.
    Kudos!

  • Keep on writing, great job!

  • If you want to obtain a good deal from this article then
    you have to apply these techniques to your won website.

  • Right now it sounds like Wordpress is the preferred blogging platform available right now.
    (from what I've read) Is that what you're using on your blog?

  • Hi there! I could have sworn I've visited this site before but after looking at many of the posts I realized it's
    new to me. Regardless, I'm definitely happy I stumbled upon it and I'll be book-marking it and checking
    back regularly!

  • Hi there I am so delighted I found your site,
    I really found you by accident, while I was looking on Google for something else, Nonetheless
    I am here now and would just like to say many thanks for a remarkable post and a all round interesting blog (I also love the theme/design), I
    don't have time to read through it all at the minute but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the excellent work.

  • I think the admin of this site is really working hard in favor of
    his web site, since here every material is quality based information.

  • Your mode of describing all in this piece
    of writing is truly pleasant, all be able to simply
    be aware of it, Thanks a lot.

  • Thanks in support of sharing such a fastidious thinking, piece of writing is nice, thats why i have read it completely

  • It's going to be ending of mine day, except before end I am reading this wonderful post to increase my experience.

  • Excellent article. Keep writing such kind of
    info on your blog. Im really impressed by your site.
    Hey there, You have done a fantastic job. I will definitely digg it and for my part recommend to my
    friends. I'm confident they'll be benefited from this website.

  • It's remarkable to pay a quick visit this web site and reading the views of all mates on the topic of this article, while I am also keen of getting know-how.

  • My brother recommended I might like this web site. He was totally right.
    This post actually made my day. You can not imagine just how much
    time I had spent for this information! Thanks!

  • I do not even know how I ended up here, but I thought this post was good.
    I don't know who you are but definitely you are going to a famous blogger if you aren't already ;) Cheers!

  • Asking questions are really good thing if you are not understanding something
    totally, but this article offers nice understanding yet.

  • What's up i am kavin, its my first occasion to commenting anywhere, when i read this post i thought i could also make comment due to this brilliant paragraph.

  • Peculiar article, exactly what I wanted to find.

  • You have made some really good points there. I looked on the internet for more info about the issue and found most people will go along with your views on this site.

  • Thanks designed for sharing such a nice opinion, article is nice, thats why i have read
    it entirely

  • Hey, I think your site might be having browser compatibility issues.
    When I look at your blog in Chrome, it looks fine
    but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then
    that, excellent blog!

  • Hello, just wanted to mention, I loved this post.
    It was helpful. Keep on posting!

  • Hello there! Do you use Twitter? I'd like to follow you if that would be ok. I'm absolutely
    enjoying your blog and look forward to new posts.

  • I am not sure if you are aware of this. I read a entry just like Solution 5 : Implementing Optional Parameters in T-SQL
    Stored Procedures - StanleyGu's Blog the other day on ... sufferin catfish I can't remember the web page just now however it was
    also about C# too. I will get back to you if it comes to me.

  • I just now wished to express I actually enjoy your writing
    skills. It is good to read an individual whom is known for a knowledge of syntax combined with spelling on-line.
    I will tell some others about Solution 5 : Implementing Optional Parameters in
    T-SQL Stored Procedures - StanleyGu's Blog. Many thanks!

  • Incredible. That was a good read. I will facebook this websites for later.
    I like your style.

Comments have been disabled for this content.