Introduction
Welcome to part III of the SQL Training series. In this final lesson, 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 the following snippet:
EXEC [dbo].[USR_Update_Employees];
SELECT * FROM [dbo].[USR_Employees];
And we can update the stored procedure with the following snippet:
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
Parameters can be used to exchange data values with application variables or Transact-SQL variables. You can use parameters in stored procedures:
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];
Next steps
Congratulations on completing our SQL training series! We challenge you to implement what you've learned in your Kepion apps.