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

A view is treated "as it were" a table by the database. Basically it's a select saved in the bank. It is used to save a view ( select ) to the bank.

As% name_name is a sequence of procedures, which can include "DML" commands such as stored procedure , select and update or "DDL", as insert for example. A stored procedure may or may not return data.

Its use is much broader than a create table , because it allows you to execute a plethora of view commands. From the point of view of SQL , its behavior is similar: it keeps a saved query, optimizing the performance, but the difference, in simply thinking of select , is that a stored procedure allows the passing of parameters, then can execute a filtered query ( select for example), since a where does not accept parameters.

VIEWS

A VIEW is basically a query sql , where we construct a query , usually more complex than a simple SELECT , and then we can use it as if it were 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 T1 and T2 table and returning results for the Id and Data columns of T1 and Texto of T2 .

The VIEWS must be created when a specific query is invoked multiple times, and to execute it suffices:

SELECT * FROM V_Exemplo

Instead of re-executing the same query , it can be tricky to manage.

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

Stored Procedure

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

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

Let's imagine that in the query we put 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 been able to get, for example, only 1,000 results from a table with 1,000,000, which is not only possible with VIEW .

VIEWS and SPs have completely different implementations, and should be used depending on the scenario in question.

The information I gave is somewhat simplistic, but I hope I have helped.

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