Create views and stored procedures

In this article

You will get to know about...

→ Views
→ Stored procedures

Introduction :

When it comes to database management, views and stored procedures are incredibly useful tools. Views are virtual tables that display the results of a SELECT statement. They allow you to simplify complex queries and provide a layer of abstraction over the underlying tables.

Stored procedures, on the other hand, are sets of SQL statements that are stored in the database and can be called and executed multiple times. They are useful for performing tasks that involve multiple SQL statements, such as data manipulation, data validation, and business logic implementation. Stored procedures can improve performance by reducing the amount of data sent between the application and the database.

Create views and stored procedures 📝

A view is a virtual table created by a stored SELECT statement. It allows you to retrieve and manipulate data as if it were a table, without actually storing the data.

A stored procedure, on the other hand, is a group of one or more Transact-SQL statements that are stored in the database and can be executed as a batch. Stored procedures are often used for implementing business logic, data validation, and performing complex data manipulation tasks.

Views are queried like tables and don't accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.

For this example, you use CREATE VIEW to create a view that selects only two of the columns in the Products table. Then, you use CREATE PROCEDURE to create a stored procedure that accepts a price parameter and returns only those products that cost less than the specified parameter value.

Create a view

Execute the following statement to create a view that executes a select statement, and returns the names and prices of our products to the user. 👇

CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;

In this code : 👆

→ Creates a view named vw_Names that selects ProductName and Price from the Products table.
→ This view allows querying ProductName and Price as if they were columns in a table, simplifying queries.

Test the view

Views are treated just like tables. Use a SELECT statement to access a view.

SELECT * FROM vw_Names;

Create a stored procedure

The following statement creates a stored procedure name sp_Names, accepts an input parameter named @VarPrice of data type money. The stored procedure prints the statement Products less than concatenated with the input parameter that is changed from the money data type into a varchar(10) character data type. Then, the procedure executes a SELECT statement on the view, passing the input parameter as part of the WHERE clause. This returns all products that cost less than the input parameter value.

CREATE PROCEDURE sp_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

In this code : 👆

→ Creates a stored procedure named sp_Names that accepts @VarPrice as a parameter of type money.
→ The procedure prints a message to the user indicating the selected products are less than the specified price.
→ It then selects ProductName and Price from the vw_Names view, filtering the results based on the @VarPrice parameter.

Test the stored procedure

To test the stored procedure, type and execute the following statement. The procedure should return the names of the two products entered into the Products table with a price that is less than 10.00.

EXECUTE pr_Names 10.00;

The SQL code includes a view `vw_Names` that selects `ProductName` and `Price` from the `Products` table, simplifying data access.

It also defines a stored procedure `pr_Names` with a parameter `@VarPrice`, which prints a message and selects `ProductName` and `Price` from `vw_Names` based on the parameter value. This illustrates how stored procedures can encapsulate logic for dynamic data retrieval.