A view represents a virtual table via a select query that is saved as a view. A frequent use case of a view is when you have business users that often join several tables together due to their relevant relationships. Instead of having the user always perform the raw join query, you can instead save a view that does this for them (see example below).
Why would you create a view?For starters, views can save business users time by allowing them to access important and relevant data sets without having to right SQL queries. Aside from the time savings to you users, you also want to think about the fact that sometimes business users need to access data to perform their roles within the company but they may not be familiar with SQL. Views reduce the need for the business user to be as familiar with SQL as if they had to build their own queries from scratch.CREATE VIEW vw_Names AS SELECT ProductName, Price FROM Products; GO What is a SQL Stored Procedure?A stored procedure is essentially a programmatic SQL routine, or sequence of actions/steps, that you’re saving to be able to use on a regular basis without having to re-type and will be executed as a batch. Stored procedures can save T-SQL actions like SELECT, PRINT, etc.
and execute them in sequence. You can also configure stored procedures to accept variables when called, which is beneficial if your users may want to execute a common procedure but some data element that run it against may vary (see example below).CREATE PROCEDURE pr_Names @VarPrice money AS BEGIN — The print statement returns text to the user PRINT ‘Products less than ‘ + CAST(@VarPrice AS varchar(10)); — A second statement starts here SELECT ProductName, Price FROM vw_Names WHERE Price < @varPrice; END GO When to use a View versus a Stored Procedure?Since views are simply saved select queries, they are most effectively used when you have common queries to run especially regular joins that are frequently needed by users. This will save time, ensure a consistent and correct query is executed, and avoid analytical inconsistency across the organization. However, if you have more complex routines you wish to store than a select query, a stored procedure should be used.
This is especially true if you would like to perform actions using custom variables or run control flow loops.