Which one is best View or Stored procedure in sql server for data access purpose

Question

I often use stored procedure for data access purpose but don't know which one is best view or SP.

Stored procedure and views are both compiled and execution plan is saved in database. So please tell me which one is best for data access purpose and why best list down the reason please.

I search google to know which one is best but got no expected answer.

Answer 1

I disagree with Jared Harding when it comes to stored procedures causing application updates to be more difficult. It's much easier to update a stored procedure than to update application code, which might need to be recompiled and require you to kick users out of the system to make an update. If you write all your SQL in stored procedures, you'll probably only have to update the application half as often as you otherwise would. And a stored procedure can be updated with no disruption to the users.

I strongly recommend use of stored procedures over views or SQL written within application code. With parameterized stored procedures that use dynamic SQL built as a string and called with the secure sp_executesql function, you can write a single stored procedure for selecting data that can be used across multiple applications and reports. There is virtually no need to use a view unless you really need to block permissions on underlying tables. It's much better to create your basic SELECT query inside a stored procedure and then use parameter options to change how the results are filtered. For example, each parameter can add a different line to your WHERE clause. By passing no parameters, you get back the full unfiltered recordset. As soon as you need to filter your results by a different field, you only need to add one parameter and one line of code in that procedure, and then have any application that needs to make use of it pass in the parameter. By defaulting your stored procedure parameters to null, you don't have to change any applications which are calling that stored procedure for other purposes.