What is the difference between a View and a Stored Procedure in SQL?

Question

I have come across SQL Views and also stored procedures. I would like to understand better, what is the difference between a view and a stored Procedure and what are its purposes?

It would be possible to provide 1 practical case for each situation?

Answer 1

One view is treated "as if it were" a table by the database. Basically is a select save to bank. Used to save a view (select) at the bank.

Already stored procedure as the name says, it is a sequence of procedures, which can include "DML" commands such as select, update and insert or "DDL", as create table for example. A stored Procedure may or may not return data.

Its use is much wider than a view, as it allows to execute a multitude of language commands SQL.
From the point of view of a select, its behavior is similar: it keeps a saved query, optimizing performance, but the difference, in thinking simply select, is that a stored Procedure allows passing parameters, so you can execute a filtered query (where for example), already a view does not accept parameters.

Answer 2

VIEWS

One VIEW is basically a consultation sql, where we built a query, usually more complex than a simple SELECT, and then we can use it as a table.

Example

CREATE VIEW V_Exemplo
AS
    SELECT      T1.Id
            ,   T1.Data
            ,   T2.Texto
    FROM        T1
    INNER JOIN  T2 ON T2.IdT1 = T1.Id

The above example will create a VIEW by joining the table T1 and T2 and returning column results Id and Data of T1 and Texto of T2.

As VIEWS should be created when a specific query is invoked multiple times, and to execute:

SELECT * FROM V_Exemplo

Instead of running it again query, that can be tricky to manage.

Heed, as VIEWS do not allow the use of parameters, so we will not be able to restrict the query in the same way as, say, in a Stored Procedure.

---

Stored Procedure

One Stored Procedure is basically a parameterizable method where we can include logic.

It is much more flexible than the VIEW, not only because it allows the use of input parameters but also because there we can execute [virtually] everything.

Let’s imagine that in the query we placed above we needed to filter the date to restrict results:

DELIMITER //
CREATE PROCEDURE SP_Exemplo (IN Data DATETIME)
BEGIN
    SELECT      T1.Id
            ,   T1.Data
            ,   T2.Texto
    FROM        T1
    INNER JOIN  T2 ON T2.IdT1 = T1.Id
    WHERE       T1.Data >= Data
END //
DELIMITER

In this case we have already obtained, for example, only 1,000 results from a table with 1,000,000, something that is not possible only with the VIEW.

As VIEWS and the SPs have completely different implementations and should be used depending on the scenario.

The directions I’ve given are somewhat simplistic, but I hope I’ve helped.

Answer 3

View is a query stored in your database, as the stored Procedure is a stored procedure. In View, you have a query that returns some result. The stored process contains a process, which can be backup start, pointers, while’s and, depending on the configuration, and the DBMS, functions and processes of the operating system itself.