SQL Stored Procedures as Front End?

  • Thread starter WWGD
  • Start date
  • Tags
    sql
  • #1
WWGD
Science Advisor
Gold Member
5,421
3,693
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) ?
 

Answers and Replies

  • #2
Borg
Science Advisor
Gold Member
1,889
2,434
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.
 
  • Like
Likes WWGD
  • #3
WWGD
Science Advisor
Gold Member
5,421
3,693
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.
Thanks^5000 (for previous) Borg. How does this compare to an Access front end in general?
 
  • Like
Likes Borg
  • #4
Borg
Science Advisor
Gold Member
1,889
2,434
Thanks^5000 (for previous) Borg. How does this compare to an Access front end in general?
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.
 
  • Like
Likes WWGD
  • #5
QuantumQuest
Science Advisor
Insights Author
Gold Member
926
485
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.
 
  • Like
Likes WWGD

Related Threads on SQL Stored Procedures as Front End?

  • Last Post
Replies
2
Views
3K
Replies
18
Views
991
  • Last Post
Replies
4
Views
2K
  • Last Post
Replies
4
Views
3K
  • Last Post
Replies
3
Views
7K
Replies
3
Views
854
  • Last Post
Replies
5
Views
841
  • Last Post
Replies
1
Views
667
  • Last Post
Replies
6
Views
712
Replies
4
Views
1K
Top