CODEWITHSUNDEEP
t-sqluser-defined-functionstemp-tables
Alberto De Caro
Alberto De Caro

Reputation: 5213

Sql Server table structure inside a user defined function

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)

Upvotes: 0

Views: 1044

Answers (2)

Andriy M
Andriy M

Reputation: 77677

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.

Upvotes: 0

Peter Wishart
Peter Wishart

Reputation: 12280

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

Upvotes: 1

Related Questions