Exotic use of User Defined Function

This title was given by one of my colleagues for the specific way I used a user defined function to solve a problem.

What were the task and the resolution?

One of our clients gave us requirements that an XML file was supposed to be FTP'd to the client when a new record appeared or an old one is changed in a table. This is simple process, nothing new. The business front end application was created, tested, and distributed to production. The main data transfer process was developed as well, and the files started flying to the client.

And suddenly client realized that they couldn’t handle their own requirements for the XML file. (What is surprise!?) There was no way for client’s load process to identify each record uniquely by the fields the client defined for the output file. We made a proposal to accept our internal primary key identifier but the client did refuse it saying that their key is char(3) while our is integer, so they can’t use it. Of course, the client didn’t want to make any adjustments to their internal database. So, we should define some mechanism to make a unique identifier with char(3) and submit it as a part of XML file.

What were the choices we had? Not many. One of the proposals was to make an application change to generate a unique key for the new column. One problem – our applications do not require this column and do not use it. Plus the application changes and revalidation time would take 2-3 weeks. Too long and too much effort required. We can’t use identity column for the key generation because the number of records in the table can be more then 999 but less then 30,000, which means that the function should use combination of letters and numbers.

This was the time when the task was presented to me by the management and the question was placed if any easier solution can be developed. I decided to add a column and use a User Defined Function as a column default for the generation of unique values in the column.

Let us see the implementation. First we will create a table CUSTOMER:

Create table CUSTOMER
(customer_id int identity(1,1), cust_nm varchar(25),
cust_address varchar(50), cust_phone varchar(12), client_cust_cd char(3))

The column client_cust_cd represents our unique column for the specifically generated key value. In the first step, I created a user defined function UDF_UID().

CREATE FUNCTION dbo.UDF_UID() 
Returns char(3)
as
Begin
declare @pidint int, @recnum int, @record_identification char(3),
        @letter_old char(1), @letter_new char(1) 
SET @letter_new = '!'
select @record_identification = max(client_cust_cd
 from CUSTOMER
SET @record_identification = upper(IsNull(@record_identification,'000'))
select @pidint = len(@record_identification) 
while (@pidint > 0)
 begin
   set @letter_old = substring(@record_identification,@pidint,1)
   select @letter_new = CASE 
        WHEN ASCII(@letter_old) >= 48 and ASCII(@letter_old) <= 56 
THEN char( ASCII(@letter_old) + 1)
        WHEN ASCII(@letter_old) = 57 
      THEN 'A'
        WHEN ASCII(@letter_old)>= 65 and ASCII(@letter_old) <= 89 
      THEN char( ASCII(@letter_old) + 1)
        ELSE CHAR(48)
     END
   set @record_identification = left(@record_identification,@pidint - 1) + 
    @letter_new + CASE
                       WHEN @pidint = len(@record_identification) 
  then ''
                       ELSE right(@record_identification,len(@record_identification) - @pidint )
                     END
    IF (@letter_new <> CHAR(48))
     begin
       break
     end
    set @pidint = @pidint - 1
 end
return @record_identification
End

The next step was to add this default to the column. It is not a straight forward process. I dropped the column and then altered the table CUSTOMER by adding the column client_cust_cd back with the default constraint.

alter table CUSTOMER drop column client_cust_cd
go
alter table CUSTOMER add client_cust_cd
char(3) null default dbo.udf_uid()
go

Lets verify the result.

Insert into CUSTOMER ( cust_nm , cust_address, cust_phone)
  Values ('Testnm','TestAddress','2153452345')
SELECT * FROM CUSTOMER
customer_id cust_nm                   cust_address                             cust_phone   client_cust_cd
----------- ------------------------- ---------------------------------------- ------------ --------------
1           Testnm                    TestAddress                              2153452345   001

It's working! The next required step is to update NULL values in the column client_cust_cd for the existing rows. The stored procedure GENERATE_CLIENT_CUST_CD is generating the unique values for the NULLs.

CREATE procedure dbo.GENERATE_CLIENT_CUST_CD
as
begin
 SET
NOCOUNT ON
DECLARE @ptrec table (tid int identity(1,1),
customer_id int)
declare @customer_id int, @cd char(3), @minid int, @maxid int
insert into @ptrec(customer_id)
  select customer_id from CUSTOMER
    where isnull(CLIENT_CUST_CD,'') = isnull(@cd,'')
select @minid = min(tid), @maxid = max(tid) 
  from @ptrec
while (@minid <= @maxid)
 begin
   select @customer_id = customer_id from @ptrec where tid = @minid
   update CUSTOMER set CLIENT_CUST_CD=dbo.udf_uid()
    where customer_id = @customer_id
   set @minid = @minid + 1
 end
End

Run the stored procedure and table is ready. From this point on User Defined Function UDF_UID() will be used as a generator of the default values. For the sake of clarity, I did simplify the table structure, User Defined Function and a value generation stored procedure.

Conclusion

I purposely named the described way of defaults setup as ‘Exotic’. You should be careful while choosing to make the defaults by using a UDF and choose it ONLY IF IT IS ABSOLUTELY NECESSARY. It allows you to generate very complex defaults based on specific business conditions with the broad ability to use advanced SQL Server functionality. But you should remember that performance issues may arise while using a UDF for the column defaults.