Method of working out check digit for an SSCC shipping code in SQL on a system with Sage Line 500 and Datalinx WHM (Although its easy enough to change, the first “set @SSCC” basically gets the digits, the rest of the code simply calculates the check digit).
This proc uses the Datalinx WHM Pallet ID and Sage stock warehouse code as Sage Stock lot numbers may contain text and slashes.
If called from a Datalinx WHM telnet/SSH script all print lines must be commented out.
GS1 codes can be shorter or longer, remember only 16 digits can be used (The GS1 code goes in from the second digit and digit 18 is always a check code!)
After needing two different versions (One required spaces in between different blocks the other didn’t) I decided to be lazy and specify the field locations. This version expects no spaces, if you need spaces simply change the Substrings and increase the variable SSCC from varchar(18).
Another thing to mind is I don’t need to change the first digit (GS1 states the same SSCC code can not be used within a year).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:      Generate an SSCC code with check digit
-- The first digit is always zero (Unless we re-use lot numbers within a year)
-- Digits 2 to 7 are our GS1 number of 1234567
-- Digits 9 to 17 are out lot number (With +0 taken off the front)
-- Digit 18 is a check digit.  Use GS1 Check Digit Rules to Calculate
-- =============================================
-- GS1 Check Digit Rules
-- 1 Starting with the right hand digit of the number, add all the alternate digit values
-- 2 Multiply the result of step 1 by 3
-- 3 Add up all the other remaining digits
-- 4 Add the result of step 2 to the result of step 3
-- 5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10
-- Check output with http://www.gs1.org/check-digit-calculator to ensure accurate
-- =============================================
-- Data sources used:
-- scheme.stquem
-- =============================================
-- Example execution
-- exec [dbo].[usp_generatecheckdigitfor_SSCC] @palletno = '+001234567', @wh = 'WH'
-- =============================================
CREATE PROCEDURE [dbo].[usp_generatecheckdigitfor_SSCC] 
       -- Add the parameters for the stored procedure here
       @palletno varchar(10) = '+000000000', 
       @wh varchar(2) = 'WH'
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
 
DECLARE @SSCC varchar(18)  --The SSCC Code
DECLARE @SSCCcd_working1 int  --For Step 1, 2 and 4 and for final calc
DECLARE @SSCCcd_working2 int  --For Step 3 and 4
DECLARE @SSCCcd_r int  --Rounded results calc
DECLARE @SSCCcd_dif int  --Used to calc check digit
 
--Input is 17 numbers for SSCC number.  A check digit must be calculated at the end
--Our GS1 Company code is 7 digit long and is "1234567".  An extra zero is added at the end as we can't send the plus symbol of out pallet id
       set @SSCC = (select '01234567' + SUBSTRING(users_text_field,2,8) as SSCC from scheme.stquem(nolock) where users_text_field = @palletno and warehouse = @wh)
       print @SSCC
 
--Work out Check Digit from @SSCC
print '1 Starting with the right hand digit of the number, add all the alternate digit values'
       set @SSCCcd_working1 = SUBSTRING(@SSCC,17,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,15,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,13,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,11,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,9,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,7,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,5,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,3,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,1,1)
       print @SSCCcd_working1
print '2 Multiply the result of step 1 by 3'
       set @SSCCcd_working1 = @SSCCcd_working1*3
       print @SSCCcd_working1
print '3 Add up all the other remaining digits'
       set @SSCCcd_working2 = SUBSTRING(@SSCC,16,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,14,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,12,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,10,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,8,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,6,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,4,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,2,1)
       print @SSCCcd_working2
print '4 Add the result of step 2 to the result of step 3'
       set @SSCCcd_working1 = @SSCCcd_working1 + @SSCCcd_working2
       print @SSCCcd_working1
print '5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10'
       set @SSCCcd_r = ROUND(@SSCCcd_working1,-1)
       print @SSCCcd_r
-- Avoid issues from rounding down, add 10 to ensure @SSCCcd_r is always greater or equals to @SSCCcd_working1
       If @SSCCcd_r < @SSCCcd_working1
              set @SSCCcd_r = @SSCCcd_r+10
       print @SSCCcd_r
--Work out difference between rounding and check digit
       set @SSCCcd_dif = @SSCCcd_r - @SSCCcd_working1
       print @SSCCcd_dif
       print @SSCC
       set @SSCC = @SSCC + CAST(@SSCCcd_dif as varchar)  --Don't treat as number!
       print @SSCC
       select @SSCC as SSCC

END