SQL Stored Procedures as Front End?

[WWGD]

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) ?

 

[Borg]

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.

 

[WWGD]

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?

 

[Borg]

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.

 

[QuantumQuest]

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.