Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Stored procedure: Dynamic WHERE clause?

    I want to write a stored procedure that takes, let's say for example, six parameters. None, one, or several of the parameters may be specified by the caller. I came across an article which discuss some different techniques for doing this. What's not clear to me is which is a recommended way to do this?

    The article is:
    http://www.codeproject.com/KB/databa...RE_Clause.aspx

    In a nutshell the methods proposed in the article are:

    Using COALESCE:

    where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
    JoiningDate >= Coalesce(@StartDate, JoiningDate) AND
    JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
    Salary >= Coalesce(@Salary, Salary)

    Using ISNULL:

    where EmployeeName = IsNull(@EmployeeName, EmployeeName) AND
    JoiningDate >= IsNull(@StartDate, JoiningDate) AND
    JoiningDate <= IsNull(@EndDate, JoiningDate) AND
    Salary >= IsNull(@Salary, Salary)

    Using CASE:

    where EmployeeName =
    Case When @EmployeeName Is Not Null Then @EmployeeName
    Else EmployeeName End AND JoiningDate >=
    Case When @StartDate Is Not Null Then @StartDate
    Else JoiningDate End AND JoiningDate <=
    Case When @EndDate Is Not Null Then @EndDate
    Else JoiningDate End AND Salary >=
    Case When @Salary Is Not Null Then @Salary
    Else Salary End

    Alternative:

    WHERE (@EmployeeName Is Null OR @EmployeeName = EmployeeName) AND
    (@Salary Is Null OR @Salary = Salary) AND
    (@StartDate Is Null OR @EndDate Is Null OR
    (@StartDate Is Not Null AND @EndDate Is Not Null AND
    JoiningDate BETWEEN @StartDate AND @EndDate))

    and also there is constructing the sql statement by string concatentation.

    Which would you use?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    I use your alternative method, however...
    ...if the table is large or there are a great many option parameters which are rarely used, then you will get greater efficiency constructing a dynamic sql statement containing only the parameters specified.
    This is one of the few instances where dynamic SQL is preferable to coded SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,634
    Hmmm...Dynamic SQL? What about security? I would try to separate the logic into independent modules, even though I already hear a bunch of arguments against it. The cleaner way is to review the design of the application itself. Having a "do-it-all" stored procedure is neat from a developer's stand point, but it's a bad idea from database performance view.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    this is pretty exhaustive on the topic:

    http://sommarskog.se/dyn-search.html

  5. #5
    Join Date
    Mar 2008
    Posts
    52
    Quote Originally Posted by rdjabarov
    The cleaner way is to review the design of the application itself. Having a "do-it-all" stored procedure is neat from a developer's stand point, but it's a bad idea from database performance view.
    I'd be happy to do it another way if there's a better practice. The stored procedure would drive an ASP.NET gridview which would need to be optionally filtered by several of its columns. The user could select to filter by any combination of parameters including no filtering at all. If you have another solution I'm open.

  6. #6
    Join Date
    Mar 2008
    Posts
    52
    Quote Originally Posted by jezemine
    this is pretty exhaustive on the topic:

    http://sommarskog.se/dyn-search.html
    Thanks for the link I did come across that article in my googling.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •