1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2017
    Rep Power

    Question Downsides using DB operations on stored procedures

    Hi there.

    I started working on a project where some Firebird databases are in use. When I was doing my first few things in the new program I used an existing stored procedure but needed some filtered data from the resultset.
    When I delivered my solution my boss sayd don't use where on a stored procedure. But he could not answer the why not.

    select * from stored_procedure_here(3) where size > 30;

    What are the downsides of this approach. Or are there any? Maybe when the procedure has massive joins in it? It could be used to page some result data without bloating the procedure with lots of extra parameters.

    Thanks for your thougths.

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Plovdiv. Bulgaria
    Rep Power
    The only reason I can thing of is maybe, because the stored procedure first get all the data and you filter the result after that, instead of filtering the data in the statements in the stored procedure.
    If you have a table with 100 000 rows and only 100 with value 30, the stored procedure will find and fetch 100 000 rows and after that you search the 100 rows with value 30. If you use a parameter to the stored procedure and use it inside the stored procedure to filter the rows in the SELECT statements where they are fetched your stored procedure will fetch and return only 100 rows.
    But often I use where in stored procedures. I'm trying to pass as many filters as possible in the procedure, but if I can't I use WHERE too.

IMN logo majestic logo threadwatch logo seochat tools logo