SQL Training - Basics III

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];