Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Unanswered: Where Clause Parameter in Views

    I have an Epicor ERP system that enables writing 'queries' in its internal format, that can see Views in the MSSQL database. Very useful, as I can write view definitions with much more flexibility than the ERP tools. As far as I can tell the ERP DB interface can't call SQL procedures (unless someone can enlighten me).
    What I want to to is to pass a value from the ERP engine that the View can use to restrict record selection. The View query produces a field that describes how far down into a nested parts list structure it has got, so the data is not in the database, it is calculated by the query.
    A user may wish to restrict the depth of descent to, say 3 levels. I can filter the results set returned by the view in today's solution to only show levels up to 3, but the View still processes all levels (some 12 million records possible return) before the filtering can be applied. SLOW...
    So I want to pass a parameter (if that is the right jargon) into the View to limit how much work it will do.
    Drawn a complete blank so far... Any ideas?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Views don't accept parameters.
    Stored procedures can.

    Views are filtered using WHERE clauses.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can include the depth in the output, allowing the user to filter on it.

    Or you can convert the view to a User-Defined Table Function, which will accept parameters.
    If it's not practically useful, then it's practically useless.

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

Tags for this Thread

Posting Permissions

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