Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Exotic use of User Defined Function

By Leo Peysakhovich,

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.

Total article views: 11054 | Views in the last 30 days: 5
 
Related Articles
ARTICLE

Customized Output Labels

SQL Server has some great solutions for writing reports easily, ensuring quick service for your cust...

FORUM

Identify letter vs. number

Parsing letters from numbers in a character entry

FORUM

Custom calculations in Dynamic Column Groups

how to perform Custom calculations in Dynamic Column Groups

FORUM

How do I get the latest record in each month for each customer?

How do I get the latest record in each month for each customer?

FORUM

Customize SQL Query

Customize

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones