GTIN 12 UPC Check Digit Calculation in SQL Server 2008

I recently needed to generate the check digit for a GTIN-12 UPC code in SQL. The calculation method for the check digit varies depending on what kind of barcode you’re implementing, so I popped on over to Google to get the necessary algorithm. Naturally, and regardless of simplicity, I checked for any available code snippets. The only I found weren’t GTIN-12, and were in C or C++. So for anyone looking to do it (or something similar) in T-SQL, here’s a scalar function to do just that.

CREATE FUNCTION dbo.CalculateBarcodeGTIN12CheckDigit(@input CHAR(11))
RETURNS INT
AS
BEGIN
DECLARE @evenDigitSum INT = 0
,@oddDigitSum INT = 0
,@i TINYINT = 0
,@result INT;

-- check if given Barcode is Numeric , if not return error status -1
IF(ISNUMERIC(@input) = 0
OR LEN(RTRIM(LTRIM(@input))) != 11)
RETURN -1

-- start the compute BarCode checksum algorithm
SET @i = 1
WHILE (@i <= 11)
BEGIN
--Add odd and even digits separately;
IF((@i % 2) = 0)
SET @evenDigitSum += CONVERT(TINYINT, SUBSTRING(@input,@i,1))
ELSE
SET @oddDigitSum += CONVERT(TINYINT, SUBSTRING(@input,@i,1))
SET @i += 1
END

--As per: http://en.wikipedia.org/wiki/Universal_Product_Code
--Multiply odd sum by 3, add to even sum, and mod 10.
SET @result = ((@oddDigitSum * 3) + @evenDigitSum) % 10;
IF(@result = 0)
RETURN 0
ELSE
RETURN 10 - @result;

RETURN -1
END
GO

Scalar functions are killer, but I’ll only ever be using it for a single record.