Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 SSCC algorithms

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 07:54:20
Here is how you get the check digit for SSCC.
CREATE FUNCTION dbo.fnGetSSCC
(
@SSCC VARCHAR(17)
)
RETURNS VARCHAR(18)
AS
BEGIN
DECLARE @Index TINYINT,
@Multiplier TINYINT,
@Sum SMALLINT

SELECT @Index = LEN(@SSCC),
@Multiplier = 3,
@Sum = 0

WHILE @Index > 0
SELECT @Sum = @Sum + @Multiplier * CAST(SUBSTRING(@SSCC, @Index, 1) AS TINYINT),
@Multiplier = 4 - @Multiplier,
@Index = @Index - 1

RETURN CASE @Sum % 10
WHEN 0 THEN @SSCC + '0'
ELSE @SSCC + CAST(10 - @Sum % 10 AS CHAR(1))
END
END


E 12°55'05.25"
N 56°04'39.16"
   

- Advertisement -