T-SQL code to create stored procedures

Contributor Brian Walker provides the code you need to create six system stored procedures to support a surrogate key architecture.

The T-SQL code in Listing 1 creates six system stored procedures in the master database. I chose the master database for convenience, but the stored procedures could be created in user databases instead (remove the "sp_" prefixes from the names before creating the stored procedures in a user database). The six stored procedures are described separately, and their parameters are listed with the descriptions for easy reference. My surrogate key architecture article includes an example database. The examples for each system stored procedure presented here are intended to be executed in the context of that database, which uses the required surrogate keys.

Note: The naming of things within this T-SQL code won't help you understand how it works. Parameters, variables, tables and columns are named rather capriciously. I humbly beg your forgiveness for my programming idiosyncrasies! These stored procedures definitely reflect my weird style.

The stored procedures contain a local variable (@TPre) that can be used to specify a table name prefix. If a table name prefix is identified with this variable, then the prefix can be omitted from table names provided as parameters.

The SQL code below should be used to add an extra table to my example database. The extra table is referenced in some examples below. The SQL code also adds a detail/child table to demonstrate the handling of foreign keys.

-- DROP TABLE ExampleDetail

-- DROP TABLE Example

CREATE TABLE Example (ExampleID int IDENTITY(1,1), MyString char(1))

INSERT Example (MyString) VALUES ('A')
INSERT Example (MyString) VALUES ('B')
INSERT Example (MyString) VALUES ('C')
INSERT Example (MyString) VALUES ('D')
INSERT Example (MyString) VALUES ('E')
INSERT Example (MyString) VALUES ('F')
INSERT Example (MyString) VALUES ('G')
INSERT Example (MyString) VALUES ('H')
INSERT Example (MyString) VALUES ('I')
INSERT Example (MyString) VALUES ('J')
INSERT Example (MyString) VALUES ('K')
INSERT Example (MyString) VALUES ('L')
INSERT Example (MyString) VALUES ('M')
INSERT Example (MyString) VALUES ('N')
INSERT Example (MyString) VALUES ('O')
INSERT Example (MyString) VALUES ('P')
INSERT Example (MyString) VALUES ('Q')
INSERT Example (MyString) VALUES ('R')
INSERT Example (MyString) VALUES ('S')
INSERT Example (MyString) VALUES ('T')
INSERT Example (MyString) VALUES ('U')
INSERT Example (MyString) VALUES ('V')
INSERT Example (MyString) VALUES ('W')
INSERT Example (MyString) VALUES ('X')
INSERT Example (MyString) VALUES ('Y')
INSERT Example (MyString) VALUES ('Z')

ALTER TABLE Example ADD CONSTRAINT ExamplePK PRIMARY KEY NONCLUSTERED (ExampleID)

CREATE TABLE ExampleDetail (ExampleDetailID int IDENTITY(1,1), ExampleID int, WhatEver char(2))

INSERT ExampleDetail (ExampleID, WhatEver) VALUES ( 5,'E1')
INSERT ExampleDetail (ExampleID, WhatEver) VALUES ( 5,'E2')
INSERT ExampleDetail (ExampleID, WhatEver) VALUES ( 6,'F1')
INSERT ExampleDetail (ExampleID, WhatEver) VALUES ( 6,'F2')
INSERT ExampleDetail (ExampleID, WhatEver) VALUES (21,'U1')
INSERT ExampleDetail (ExampleID, WhatEver) VALUES (21,'U2')

ALTER TABLE ExampleDetail ADD CONSTRAINT ExampleDetailPK PRIMARY KEY NONCLUSTERED (ExampleDetailID)

ALTER TABLE ExampleDetail ADD CONSTRAINT ExampleDetailFK FOREIGN KEY (ExampleID) REFERENCES Example (ExampleID)

DELETE FROM Example WHERE ExampleID IN (3,12,13,14,25,26)



Utilities to maintain a surrogate key architecture

 Home: Introduction
 Part 1: What are surrogate key values
 Part 2: T-SQL code to create stored procedures
 Part 3: Utility routine: sp_CheckRowCounts
 Part 4: Utility routine: sp_CheckKeyValues
 Part 5: Utility routine: sp_ResetNextKeyValue
 Part 6: Utility routine: sp_OrganizeKeyValues
 Part 7: Utility routine: sp_FindDuplicateRows
 Part 8: Utility routine: sp_ChangeParentValue

More advice:
Surrogate key architecture to perform powerful database operations
Framework to support a surrogate key architecture


ABOUT THE AUTHOR:   
Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget
This was first published in March 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close