Sql Server table structure inside a user defined function

Question

In my ecommerce portal I receive products EAN codes from distributors. I want to complete EAN-12 codes computing the checksum digit, and so returning the EAN-13.

Original codes are read from a SQL Server table and are copied in another SQL Server database through a stored procedure.

I would like to compute the checksum digit inside the existing stored procedure, without adding other programming layers. The algorithm for computing the checksum is based on a digit mask (it multiplies the EAN digits by positional weight values, sums the results and computes the difference with the next greater 10 multiple).

Quite easy, a UDF could implement the algorithm, but it requires to use a temp table to handle the operations on the digits and SQL Server does not allow using temp tables inside UDF!! Do you know any work around?

Algorithm details and sample:

EAN-12:   7  2  5  1  8  4  6  6  0  4  0  5
weights:  1  3  1  3  1  3  1  3  1  3  1  3
multip:   7 6  5  3  8 12  6 18  0 12  0 15
sum:      92
checksum:  8 (= 100 - 92)

Ok, I know the trivial solution, i.e. defining 12 variables inside the UDF, one for each EAN-12 digit. But is sucks :( — Alberto De Caro
– Alberto De Caro, Commented Mar 9, 2012 at 11:44
Why is 10 equal to 100 - 92? I'm sorry if it's a dumb question to you, I know nothing about EAN-12/13. — Andriy M
– Andriy M, Commented Mar 9, 2012 at 12:24

Peter Wishart · Accepted Answer · 2012-03-09 12:25:46Z

1

You could do it without using a temp table (code is for SQL 2008):

  CREATE FUNCTION [dbo].[ac_fnEan12ToEan13](@input varchar(12))
  RETURNS varchar(13)
  AS
  BEGIN
    declare @weights varchar(12) = '131313131313'
    declare @loop int = len(@input)
    declare @sum int = 0
    while @loop > 0
    begin
      set @sum = @sum + cast(SUBSTRING(@weights, @loop, 1) as int) * cast(SUBSTRING(@input, @loop, 1) as int)        
      set @loop = @loop -1
    end
    return @input + cast((10*ceiling(@sum / 10.0)) - @sum as varchar(1))
  END
  go

[Edit] But if you wanted to you can use table variables in functions too, they're more efficient than temp tables:

    declare @values table (digit int, value int, primary key (digit))
    insert @values select 1, 7

answered Mar 9, 2012 at 12:25

Peter Wishart

12.5k1 gold badge29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andriy M · Accepted Answer · 2012-03-09 12:42:20Z

0

The following function will calculate the checksum according to your description. It doesn't require a temporary table, though it does use a set-based approach to accomplish the task:

CREATE FUNCTION dbo.GetEAN13CheckSum (@EAN12 varchar(12))
RETURNS int
AS BEGIN
  DECLARE @result int;
  WITH EAN12_split AS (
    SELECT
      weight = 3 - number % 2 * 2,
      digit  = CAST(SUBSTRING(@EAN12, number, 1) AS int)
    FROM master..spt_values
    WHERE type = 'P'
      AND number BETWEEN 1 AND 12
  )
  SELECT @result = 10 - SUM(weight * digit) % 10
  FROM EAN12_split;
  RETURN @result;
END

Of course, you can modify it so it adds the checksum to the EAN-12 code and returns the result as an EAN-13 string value.

answered Mar 9, 2012 at 12:42

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Collectives™ on Stack Overflow

Sql Server table structure inside a user defined function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related