SQL Training - Basics III – Kepion Support Center

Welcome to part III of the SQL Training series. To follow along with the series, please ensure that you have the Integrated Financial Planning Application installed into Kepion. To download the application, go to the Sample Application page. In this article, we will review how to create and edit Views, Tables, and Stored Procedures.

View

Create View

Views are virtual tables that can be customized to return a particular result-set. Views are useful because querying a view always returns the current state of the data. Views are stored in the database and can be re-used in other queries. 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 here:

CREATE TABLE [dbo].[USR_Employees]
(
  [EmployeeCode] [nvarchar](200) NOT NULL
);

Stored Procedure

Stored Procedures are prepared SQL statements we can run over and over.

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