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.
In [ ]:
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.
In [ ]:
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.
In [ ]:
USE demo
EXEC usp_LoadPersonTable
SELECT * FROM Person
Always clean up after yourself when creating examples.
In [ ]:
USE demo
DROP PROCEDURE usp_LoadPersonTable
DROP TABLE Person
DROP TABLE PersonStageTable
