Using User Defined Functions in Table Inserts and Seeding with Stored Procedures

• Download script - 1.66 KB

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
)

-- We will add some sample data...

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

   -- Get the Person referenced by @PersonID
   DECLARE @Person VARCHAR(100) = (SELECT [Name]
                     FROM Person
                     WHERE ID = @PersonID)

   -- A VARCHAR copy of the @ID input
   DECLARE @RET VARCHAR(10) = CAST(@ID AS VARCHAR(10))

   -- CHAR Length of the @RET variable
   DECLARE @LEN INT = LEN(@RET)

   SET @RET = UPPER(LEFT(@Person,3)) +     -- Magic (see BOL for
            STUFF(@RET,1,0,LEFT('0000000',7 - @LEN)) -- Function
                           -- definitions/syntax

   RETURN @RET

 END

-- Now the all important Job table that calls our UDF as a reference
 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),       -- UDF Reference
   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 the data...
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 the data...
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;

  -- Get the JobID
  DECLARE @JobID INT = (SELECT ID
    FROM Job
    WHERE JobCode = @JobCode)

  -- Get the largest TaskNo for the job
  DECLARE @MAX INT = (SELECT MAX(TaskNo)
                FROM Task t
              JOIN Job j
                ON t.JobID = j.ID
                WHERE j.JobCode = @JobCode)

  -- Fancy error handling block
  BEGIN TRY
    BEGIN TRANSACTION

  -- Check TaskNo is valid else set defaults
  SET @TaskNo = (
         SELECT CASE ISNULL(@TaskNo,0)     -- if @TaskNo is null then check @MAX
          WHEN 0
          THEN
            CASE ISNULL(@MAX,0) -- if max is null then set @TaskNo = 1
              WHEN 0
              Then 1
              ELSE @MAX + 1
              END
          ELSE                -- Else if @TaskNo is not null Then
            Case WHEN @TaskNo > @MAX   -- if @TaskNo > @MAX
                      -- set to @MAX + 1
                 THEN @MAX + 1
                 ELSE @TaskNo         -- else do nothing
                 END
          END)

  DECLARE @Count INT = @MAX

  WHILE @Count >= @TaskNo      -- If @MAX >= @TaskNo Update TaskNos >=
    BEGIN             -- @TaskNo

      UPDATE Task
        SET TaskNo = TaskNo + 1
      WHERE JobID = @JobID
        AND TaskNo = @Count

      SET @Count = @Count - 1         -- Decrement Counter

    END

  INSERT Task(JobID, TaskNo, TaskDescription) -- Perform Insert
    VALUES(@JobID, @TaskNo, @TaskDescr)

    COMMIT TRANSACTION                -- Commit transaction

  END TRY

  -- Perform Error Handling
  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 TaskNos, 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' -- Default position
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

• Version 1.0