RelationalDBDesign RelationalDBDesign



Database Design   «Prev 

Stored Queries created using SQL

User views are often referred to as stored queries because they are created with SQL used as a data-definition language (DDL) and retrieved with SQL used as a data-modeling language (DML).
In the absence of stored queries, users can simply create queries on the fly; but then they must specify what data from which tables they would have liked to look at.
User views have that specification already built in. In fact, that is what a DDL (Data Definition Language ) actually creates and stores: the specification. The designer gives the specification a name (which becomes the user view); the user uses SQL as a DML to invoke the user view by name.
The below answers are useful as a background for 'stored procedures'.
Is a 'stored procedure' just when I have a database table that contains queries that can be called? ie, something like this

INDEX | NAME          | QUERY
1     | show_names    | "SELECT names.first, names.last FROM names;"
2     | show_5_cities | "SELECT cities.city FROM cities LIMIT 0,5;"


There are specific benefits and disadvantages to using the natively implemented stored procedures (or delayed parameter binding).
Whether its right for you depends on what you are trying to achieve, for example performance, ease of maintenance, and access control.