Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

SQL Stored Procedures as Front End?

Tags:
  1. Apr 26, 2016 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi,
    Just curious: is it possible/feasible to use SQL stored procedures as a front end? I mean, couldn't we
    design stored procedures to allow for easier querying if we knew the general type of queries that would be used for the application (of course, the application for which we designed the back-end SQL) ?
     
  2. jcsd
  3. Apr 26, 2016 #2

    Borg

    User Avatar
    Science Advisor
    Gold Member

    I believe that you can do that but it's been a while since I've done it. I'll have to dig around for an example if you want one.
     
  4. Apr 26, 2016 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks^5000 (for previous) Borg. How does this compare to an Access front end in general?
     
  5. Apr 26, 2016 #4

    Borg

    User Avatar
    Science Advisor
    Gold Member

    I don't know about Access. I remember seeing a trick like this in an Oracle database about four years ago. If I remember correctly, it's an Oracle View.
     
  6. Apr 26, 2016 #5

    QuantumQuest

    User Avatar
    Gold Member

    You can use stored procedures - I've used them a lot, to talk in my own experience, as they have four crucial benefits: performance - the compilation is done on database server, so subsequent requests have performance gain, security - stored procedures are database objects, so you can take security measures and prevent some nasty things to happen, like SQL injection attacks, encapsulation - talking essentially about business logic in one place and of course last but not least, code reuse. However, stored procedures design and implementation is waste of time if simple queries is the case. That's because code must be written in the application to call the stored procedure, pass the parameters it should, check the result and return data.
    A more preferred way is Object Relational Mapping, as ORM essentially creates a representation of the data model. So effectively you can access your database without SQL code or constructs. Especially for CRUD operations that represent a very high percentage of total operations to be executed, ORMs create parameterized queries automatically.
    In general, ORMs have more benefits in many cases than stored procedures, like rapid app development, less code to be written, good maintainability, performance and they don't break the N-Tier model - something that stored procedures do.
    Of course there are counter - arguments for them too, as lower performance than stored procedures, much overhead, less security and others, which in most cases are of minimum impact.
    In my opinion the situation at hand is the best judge about what to use and how. In my experience, after ORM became widespread, I tend to use it more and use stored procedures for more "special" reasons. Such reasons include: big number of records pushed in a single statement, legacy applications (that you can't but use them), some complex query situations and data aggregation.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: SQL Stored Procedures as Front End?
  1. Storing sql in MySQL (Replies: 2)

  2. SQL basics (Replies: 4)

  3. SQL relationships (Replies: 3)

  4. Sql join? (Replies: 4)

  5. SQL Help (Replies: 2)

Loading...