- 1). Define your view name. The "Create" keyword in SQL creates database objects such as tables, stored procedures and views. To create a new view object, the statement is used along with the name of the view. The following commands are the first statements used to create a view:
create view newView
as
Everything after the "as" statement is the code that retrieves data from the database. In this example, a new view called "newView" is created. - 2). Create the code that retrieves data. The code in a view is the same as a stored procedure. The only difference between a view and a stored procedure is that a view cannot take parameters from the user. The view is static, which means the data returned is always the same. However, you can filter the data when you call the view from your software. Below is an example of a view that retrieves all customers with their associated orders:
create view newView
as
select customerId, first_name, last_name, orderId, order_date
from customer join orders where customer.customerId=orders.customerId
Press the F5 key to execute this code. The new view is created on your SQL Server. - 3). Call your view to test its function. Now that the view is created, you can test it by calling it from your SQL code. The view example in step 2 retrieves all customers and their orders. You can now call the view to retrieve a list of filtered customers and orders. In this example, the view is used to retrieve orders from customers whose last name is "Smith." The following code is an example of how to retrieve data from a view:
select * from newView where last_name = 'Smith'
previous post