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];
Comments
0 comments
Article is closed for comments.