Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server / SQL Server 2008

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

Rate me:
Please Sign up or sign in to vote.
4.78/5 (7 votes)
26 Aug 2009CPOL3 min read 43.4K   180   29   2
Generate unique column values using UDFs and Stored Procedures

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:

SQL
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...):

SQL
 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:

SQL
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:

SQL
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:

SQL
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...

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Australia Australia
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.

Comments and Discussions

 
GeneralGood article Pin
Henry Minute26-Aug-09 3:17
Henry Minute26-Aug-09 3:17 
GeneralRe: Good article Pin
Andy_L_J26-Aug-09 9:37
Andy_L_J26-Aug-09 9:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.