Menu
I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ?
So considering performance and other aspects when and why should I prefer one over the other ?
525
A view is essentially a saved SQL statement. Therefore, I would say that in general, a stored procedure will be likely to be faster than a view IF the SQL statement for each is the same, and IF the SQL statement can benefit from optimizations. Otherwise, in general, they would be similar in performance.
There could be scenarios where you want to use a Stored Procedure in view and Join the result set returned by a Stored procedure with some other tables/views. Step 2: Create Stored Procedure that will return all results.
Stored procedures can be invoked explicitly by the user. It's like a java program , it can take some input as a parameter then can do some processing and can return values. On the other hand, trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).
Stored procedures promote bad development practices, in particular they require you to violate DRY (Don't Repeat Yourself), since you have to type out the list of fields in your database table half a dozen times or more at least. This is a massive pain if you need to add a single column to your database table.
Well, I'd use stored proc for encapsulation of code and control permissions better.
A view is not really encapsulation: it's a macro that expands. If you start joining views pretty soon you'll have some horrendous queries. Yes they can be JOINed but they shouldn't..
Saying that, views are a tool that have their place (indexed views for example) like stored procs.
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
