Welcome to part III of the SQL Training series. In this article, we will review how to create and edit Views, Tables, and Stored Procedures.
Tip: If you haven't completed the first and second parts of this series, we strongly recommend you go back and do so.
View
Views are virtual tables that can be customized to return a particular result-set. They are useful because querying them always returns your data's current state. Views are stored in the database and can be re-used in other queries.
Create View
To create a new view to show all US employees, we can write:
CREATE VIEW [dbo].[USR_US_Headcount] AS
SELECT * FROM [dbo].[D_Employee]
WHERE [Entity] = N'United States';
Note: Try not to nest multiple views (one view based on another view) as this can impact performance.
Query View
We can query a view just like a table:
SELECT * FROM [dbo].[USR_US_Headcount];
Update View
To update a view, we can use the ALTER VIEW statement.
ALTER VIEW [dbo].[USR_US_Headcount] AS
SELECT * FROM [dbo].[D_Employee]
WHERE [Entity] = N'United States'
AND [Department] IN (N'Sales', N'Marketing');
Drop View
We can delete, or 'drop,' a view with the DROP VIEW statement.
DROP VIEW [dbo].[USR_US_Headcount];
Table
We can create and drop tables in the database in the same way as views, with the CREATE TABLE and DROP TABLE statements.
Let's create a new table:
CREATE TABLE [dbo].[USR_Employees]
(
[EmployeeCode] [nvarchar](200) NOT NULL
);
Stored Procedure
Stored Procedures are prepared SQL statements we can run repeatedly.
To create a new Stored Procedure:
CREATE PROCEDURE [dbo].[USR_Update_Employees]
AS
BEGIN
TRUNCATE TABLE [dbo].[USR_Employees];
INSERT INTO [dbo].[USR_Employees] ([EmployeeCode])
SELECT [MemberLabel]
FROM [dbo].[D_Employee]
WHERE [Entity] = N'United States';
END;
We can run the Stored Procedure we created with:
EXEC [dbo].[USR_Update_Employees];
SELECT * FROM [dbo].[USR_Employees];
And we can update the Stored Procedure with:
ALTER PROCEDURE [dbo].[USR_Update_Employees]
AS
BEGIN
TRUNCATE TABLE [dbo].[USR_Employees];
INSERT INTO [dbo].[USR_Employees] ([EmployeeCode])
SELECT [MemberLabel]
FROM [dbo].[D_Employee]
WHERE [Entity] = N'United States'
AND [Department] IN (N'Sales', N'Marketing');
END;
Parameters
We can create Stored Procedures with parameters:
ALTER PROCEDURE [dbo].[USR_Update_Employees]
@Department nvarchar(50)
AS
BEGIN
TRUNCATE TABLE [dbo].[USR_Employees];
INSERT INTO [dbo].[USR_Employees] ([EmployeeCode])
SELECT [MemberLabel]
FROM [dbo].[D_Employee]
WHERE [Entity] = N'United States'
AND [Department] = @Department;
END;
And then pass values into the parameters upon execution:
EXEC [dbo].[USR_Update_Employees] @Department = N'Marketing';
Array Parameters
We can pass in array parameters:
ALTER PROCEDURE [dbo].[USR_Update_Employees]
@Department nvarchar(50),
@Entity [dbo].[tLabelSet] READONLY
AS
BEGIN
TRUNCATE TABLE [dbo].[USR_Employees];
INSERT INTO [dbo].[USR_Employees] ([EmployeeCode])
SELECT [MemberLabel]
FROM [dbo].[D_Employee]
WHERE [Entity] IN (SELECT [Label] FROM @Entity)
AND [Department] = @Department;
END;
And then execute them like this:
DECLARE @Entity [dbo].[tLabelSet];
INSERT INTO @Entity ([Label]) VALUES (N'United States');
INSERT INTO @Entity ([Label]) VALUES (N'Australia');
EXEC [dbo].[USR_Update_Employees]
@Department = N'Marketing',
@Entity = @Entity;
SELECT * FROM [dbo].[USR_Employees];
