best way to write stored procedure with option parameter for where clause

Posted on 2011-04-20
Last Modified: 2012-05-11
What's the best way to create a stored procedure in SQL 2005 so it will run whether or not a parameter is passed in to it? See sample code.

Sometimes I want to run the select statement with no where clause. But if I pass in a parameter when I execute the stored procedure, then I want the where clause applied.

I thought of doing
"where col1 like %@param1%", is that a good approach? If I set the default value of the variable to be null or something like that?

Any suggestions?
create procedure MySP
(if I pass in a parameter)
select * from tab1 where col1 = @param1
(if I don't pass in a parameter)
select * from tab1

Open in new window

Question by:GordonPrince
    LVL 40

    Expert Comment

    You can create the SP like this.
    create procedure MySP @param1 int = null
    select * from tab1 where col1 = @param1 or (col1 = col1 and @param1 is null)

    Open in new window

    LVL 9

    Accepted Solution

    this is the simple ...

        @param1 varchar(40)  
        select * from tab1 where col1 = coalesce (  @param1 , col1 )

    LVL 4

    Author Closing Comment

    Very elegant. Thanks.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now