Overview
This article (my first), demonstrates how to automatically set column values with INSERT
statements. I show two methods: The first automatically generates a unique code on a Table INSERT
using a User-defined Function; while the second uses a Stored Procedure to insert Rows into a table with specified or default index numbers.
Background
Do you ever need to seed index columns like task numbers or steps in a sequence? How about automatically generating a unique code for a customer? My solutions to these problems follow. These may not be the best, or most elegant ways to implement this functionality, but it is a good starter.
The first thing we have to do is open SQL Management Studio (SSMS) and create a sample database named DemoTaskList
; then in a new query window, we create our first table:
USE DemoTaskList
GO
CREATE TABLE Person(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) UNIQUE NOT NULL
)
INSERT Person([Name]) VALUES('Richard Nixon')
INSERT Person([Name]) VALUES('Bill Clinton')
GO
The next thing we need to do is create a User-defined function to do the string
manipulation required to automatically create a Unique Value from a combination of the Person.Name
column in the Person Table
and the ID of a Job in the Job Table
(coming soon...):
CREATE FUNCTION udf_SET_JobCode(
@PersonID INT,
@ID INT
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @Person VARCHAR(100) = (SELECT [Name]
FROM Person
WHERE ID = @PersonID)
DECLARE @RET VARCHAR(10) = CAST(@ID AS VARCHAR(10))
DECLARE @LEN INT = LEN(@RET)
SET @RET = UPPER(LEFT(@Person,3)) +
STUFF(@RET,1,0,LEFT('0000000',7 - @LEN))
RETURN @RET
END
CREATE TABLE Job(
ID INT Identity(1,1) NOT NULL PRIMARY KEY,
PersonID INT NOT NULL FOREIGN KEY REFERENCES Person(ID),
JobCode AS dbo.udf_SET_JobCode(PersonID, ID),
JobDescription VARCHAR(200) NOT NULL
)
As you can see in this line above: JobCode AS dbo.udf_SET_JobCode(PersonID, ID)
, the column definition for JobCode
references our User-defined Function and passes two parameters: PersonID
and ID
and returns for us our custom value.
The Custom value returned by the UDF is comprised of the first three letters of the person name and the auto-generated Identity value padded to 7 digits. Read the BOL documentation on the STUFF
Function to understand how it works - needless to say, it stuffs a String
into another String
.
Let's test this by adding a couple of new Jobs for Richard Nixon:
INSERT Job(PersonID,JobDescription)
VALUES(1,'Become President of the USA')
INSERT Job(PersonID,JobDescription)
VALUES(1,'Become Infamous')
SELECT * FROM Job
GO
RESULT:
===========================================================
ID PersonID JobCode JobDescription
---- ------------------- ---------- ---------------------
1 1 RIC0000001 Become President of the USA
2 1 RIC0000002 Become Infamous
As expected, a JobCode was automatically generated for our Person with each INSERT
. Let's add a job for Bill Clinton:
INSERT Job(PersonID, JobDescription)
VALUES(2, 'Achieve Notoriety')
GO
SELECT * FROM Job
GO
RESULT:
===========================================================
ID PersonID JobCode JobDescription
---- ------------------- ---------- ---------------------
1 1 RIC0000001 Become President of the USA
2 1 RIC0000002 Become Infamous
3 2 BIL0000003 Achieve Notoriety
Is that all clear? Now I want to build a Table
that will hold the tasks required to complete the Job that we have assigned to a person. We want the Tasks to be listed sequentially when we query the table, and we want to be able to insert new tasks at specified indexes in the list. For example, if no tasks exist for a Job, we will add the new Task at Task Number 1. However if tasks do exist, we can insert the task at a specified position in the list or at the end of the list depending on your criteria. Let's look at the script:
CREATE TABLE Task(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
JobID INT NOT NULL FOREIGN KEY REFERENCES Job(ID),
TaskNo INT NOT NULL,
TaskDescription VARCHAR(200)
)
GO
CREATE PROCEDURE usp_INSERT_Task(
@JobCode VARCHAR(10),
@TaskNo INT = NULL,
@TaskDescr VARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @JobID INT = (SELECT ID
FROM Job
WHERE JobCode = @JobCode)
DECLARE @MAX INT = (SELECT MAX(TaskNo)
FROM Task t
JOIN Job j
ON t.JobID = j.ID
WHERE j.JobCode = @JobCode)
BEGIN TRY
BEGIN TRANSACTION
SET @TaskNo = (
SELECT CASE ISNULL(@TaskNo,0)
WHEN 0
THEN
CASE ISNULL(@MAX,0)
WHEN 0
Then 1
ELSE @MAX + 1
END
ELSE
Case WHEN @TaskNo > @MAX
THEN @MAX + 1
ELSE @TaskNo
END
END)
DECLARE @Count INT = @MAX
WHILE @Count >= @TaskNo
BEGIN
UPDATE Task
SET TaskNo = TaskNo + 1
WHERE JobID = @JobID
AND TaskNo = @Count
SET @Count = @Count - 1
END
INSERT Task(JobID, TaskNo, TaskDescription)
VALUES(@JobID, @TaskNo, @TaskDescr)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
BEGIN
RAISERROR('Error when processing INSERT statement or Task Table.',15,1)
RETURN -100
END
END CATCH
RETURN
END
That's about all there is to it - we iterate down the TaskNo
s, increasing them by 1 and then INSERT
the new record at the specified position, like so...
EXEC dbo.usp_INSERT_Task 'RIC0000001',NULL,'Run for Class President'
GO
EXEC dbo.usp_INSERT_Task 'RIC0000001',1,'Join Student Union'
GO
EXEC dbo.usp_INSERT_Task 'RIC0000001',5, 'Run for President of USA'
GO
SELECT p.[Name], j.JobCode, t.TaskNo, t.TaskDescription
FROM Task t
JOIN Job j
ON t.JobID = j.ID
JOIN Person p
ON j.PersonID = p.ID
WHERE p.ID = 1
ORDER BY p.[Name], j.JobCode, t.TaskNo
GO
RESULT:
=======================================================================================
Name JobCode TaskNo TaskDescription
---------------------------------------------------------------------------------------
Richard Nixon RIC0000001 1 Join Student Union
Richard Nixon RIC0000001 2 Run for Class President
Richard Nixon RIC0000001 3 Run for President
As you can see, the stored procedure is able to determine the default position according to the value of @MAX
and INSERT
the new row with the correct TaskNo
value according to our rules.
Points of Interest
The UDF uses a variety of String
Functions: CAST
, LEN
, LEFT
, and STUFF
. Refer to Books On Line for detailed explanations of how to use these built-in functions.
Thanks
Thanks to Andy Taslim and Peter Gfader at UTS/SSW.com.au for their valuable advice over the last few weeks, and all the guys and gals on CodeProject who have given me the confidence to post my first article.
Version
Born in New Zealand and transported to Australia for crimes against good taste in the eighties.
Employed by large Manufacturing Co to Coordinate Production Processes, Integrate new technologies, Implement new, in-house MRP/ERP.
Out of my depth, challenged and love it.