| Author |
View vs. Stored Procedure vs. User-defined Function
|
|
| amyland 2002-05-08, 2:09 pm |
| I am very confused about these terms.
What is the better choice in what certain circumstances?
for example if a scenario like following:
Customers retrieve their own order information from a database.
Order | Item | Product
-----------+-----------+--------
OrderID | ItemID | PruductID
CustomerID | OrderID | Desciption
OrderDate | ProductID|
| Price |
The table Order has a primary key(OrderID) which is irrelevant to the customers. which method I should use to display order information for a customer?
Create a view or a stored procedure or a user-defined function?
Even though my instinct told me to choose SP, I can't persuade myself what is wrong with the other two.
Thanks in advance! | |
| baruugh 2002-05-08, 4:21 pm |
| Your instincts are right in this case, in my opinion.
Think of it like an online bank where a person wants to retrieve their account details. Obviously the customer will have to insert a parameter for them to be able to retrieve their own details (ie, like a customer number) and not anyone elses.
This rules out views, which don't accept parameters.
A UDF would be good if you needed to manipulate the data, but as you don't seem to need to then an SP would be the way to go.
Hope this helps | |
| amyland 2002-05-08, 4:55 pm |
| Thank you, baruugh.
It surely helps a lot!!! | |
| baruugh 2002-05-08, 6:54 pm |
| Happy to help. I'm sure it won't be too long before you're answering my queries! | |
|
| User-defined functions cannot modify the data base. They do accept parms though. Just be careful with what you're trying to accomplish. And remember that the exam world is different from the real world (IRL).
IRL=in Real Life | |
|
| quote:
Originally posted by amyland
Even though my instinct told me to choose SP, I can't persuade myself what is wrong with the other two.
As for view vs sp, ask yourself where you want to keep your logic. If you implement this as a view, you'll still have clients passing SQL queries on that view. If you have a stored procedure, the clients would simply call the stored procedure with whatever parameters needed.
On the flip side, if you use SPs you'll be writing more TSQL code. Not everyone likes that... |
|
|
|