Lesson 38. Stored Procedures

Everything that you have been reading about has been leading up to this section.

Stored Procedures are the workhorse object of SQL Server. They are how you get things done like loading data warehouses.

Like views, stored procedures are code that can be executed over and over. Unlike views, you can use programmability elements like variables and control flow. You can even pass variables into a stored procedure.

As an example, let’s go back and use the example from MERGE and turn it into a stored proc.

Examples

Stored Procedure Creation

First, let's create and populate some sample data.

USE demo

DROP TABLE IF EXISTS Person
DROP TABLE IF EXISTS PersonStageTable

CREATE TABLE Person(
PersonID BIGINT NOT NULL,
FirstName NVARCHAR(50) NULL,
LastName NVARCHAR(50) NULL,
SourceSystemKey NVARCHAR(50) NULL,
)

CREATE TABLE PersonStageTable(
PersonID BIGINT NOT NULL,
FirstName NVARCHAR(50) NULL,
LastName NVARCHAR(50) NULL,
SourceSystemKey NVARCHAR(50) NULL,
)

INSERT INTO Person(PersonID, FirstName, LastName, SourceSystemKey)
SELECT 1, 'Bob', 'Wakefield',1

INSERT INTO PersonStageTable(PersonID, FirstName, LastName, SourceSystemKey)
SELECT 1,'Bob','Johnson',1
UNION 
SELECT 2,'Sally','Ride',2

SELECT * FROM Person

SELECT * FROM PersonStageTable

Now let's create our proc.

USE demo

GO

DROP PROCEDURE IF EXISTS usp_LoadPersonTable

GO

CREATE PROCEDURE usp_LoadPersonTable AS

BEGIN

MERGE Person AS target
USING (
SELECT
PersonID,
FirstName,
LastName,
SourceSystemKey
FROM PersonStageTable
) AS source
ON (target.SourceSystemKey = source.SourceSystemKey)

WHEN NOT MATCHED THEN
INSERT (
PersonID,
FirstName,
LastName,
SourceSystemKey
)
VALUES (
PersonID,
FirstName,
LastName,
SourceSystemKey
)

WHEN MATCHED THEN
UPDATE
SET
target.PersonID = source.PersonID,
target.FirstName = source.FirstName,
target.LastName = source.LastName,
target.SourceSystemKey = source.SourceSystemKey
;

END

Now let's run it and see if Bob's last name got changed.

USE demo

EXEC usp_LoadPersonTable

SELECT * FROM Person

Always clean up after yourself when creating examples.

USE demo

DROP PROCEDURE usp_LoadPersonTable
DROP TABLE Person
DROP TABLE PersonStageTable