walt @ truman :: blog

Here's my first attempt at writing a UDF in T-SQL for Microsoft SQL Server 2000. We had a third party application that used a Microsoft SQL server backend. The application couldn't be customized but we needed a checkdigit added to the database when it added a record. To do this, I hacked out this ugly luhn function and then added some triggers to the table.

Sample Trigger:

CREATE Trigger AutoCheckDigitInsert
ON dbo.tablename
FOR INSERT
AS
UPDATE [tablename] SET checkdigit = dbo.luhn(isonumber.isonumber+''+tablename.idnumber) FROM
tablename, isonumber, inserted WHERE tablename.checkdigit IS null) AND (tablename.idnumber = inserted.idnumber)

This function will return the checkdigit for whatever argument you pass. For more information about the Luhn function, also known as Mod 10, see the Wikipedia article.

Download Luhn Function

CREATE FUNCTION luhn (@s varchar(20) )
RETURNS int
AS
BEGIN
DECLARE @i int, @j int, @total int, @final int, @tmp int, @result bit

SELECT @i = 2
SELECT @final = 0
SELECT @total = 0
SELECT @s =ltrim(@s)
SELECT @s =rtrim(@s)
WHILE @i < (len(@s) + 2) BEGIN SELECT @j=(ASCII(substring(@s,@i-1,1))-48) IF @i % 2 = 0 SELECT @j = @j * 2 IF @j > 9
SELECT @j = @j - 9
SELECT @tmp = @total
SELECT @total=@tmp+@j
SELECT @i=@i+1
END
IF @total % 10 = 0
SELECT @final = 0
ELSE
SELECT @final = 10 - (@total % 10)
RETURN (@final)
END