Codis de barres

Funció del SQLServer per calcular el dígit de control d’un codi EAN13:

CREATE FUNCTION [dbo].[fn_EAN13] (@Barcode nvarchar(12)) 
RETURNS nvarchar(13) 
AS 
BEGIN 
    DECLARE @SUM int , @COUNTER int, @RETURN varchar(13), @Val1 int, @Val2 int 
    SET @COUNTER = 1 SET @SUM = 0 

    WHILE @Counter < 13 
    BEGIN 
        SET @VAL1 = SUBSTRING(@Barcode,@counter,1) * 1 
        SET @VAL2 = SUBSTRING(@Barcode,@counter + 1,1) * 3 
        SET @SUM = @VAL1 + @SUM 
        SET @SUM = @VAL2 + @SUM 
        SET @Counter = @Counter + 2; 
    END 
    SET @SUM=(10-(@SUM%10))%10

    SET @Return = @BARCODE + CONVERT(varchar,((@SUM))) 
    RETURN @Return 
END

OITM -> CodeBars

OBCD -> BcdCode, ItemCode (join a la OITM), BcdName

Comprovació de tots els articles que tenen el codi de barres informat erroneament:

SELECT IT.ItemCode,IT.CodeBars,dbo.fn_EAN13(SUBSTRING(IT.CodeBars,1,12))
FROM OITM IT
JOIN OBCD CB on CB.ItemCode=IT.ItemCode
WHERE LEN(IT.CodeBars)>0 and dbo.fn_EAN13(SUBSTRING(IT.CodeBars,1,12))<>IT.CodeBars