SQL stored procedure append the result in the end

April 12, 2022

This is a "check digit" sql stored procedure in sql server. You insert a bar code number, and it makes some calculations to return a single digit.

https://www.gs1.org/services/check-digit-calculator

Also, it removes letters when running a variable.
For example, inserting "abc123456789012", the result is "8".

This works normally. If I try to run the procedure by:

SELECT dbo.checkDigittest('abc123456789012');

The result is "8".

However, I need it to keep the number that I give it, and display the result in the end.

For example, if I put abc123456789012, the result should be 1234567890128. The "8" should be appended to the end. Same for any other "check digit" number inserted.

Here is the main procedure. It removes the letter, then calculates check digits. How can I modify the result accordingly?

EXTRA: Keep in mind that CONCAT does not work in this current SQL version.

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER FUNCTION [dbo].[CheckDigittest]
       (
       @ACode NVARCHAR(100)
       )
   RETURNS NVARCHAR(1000)
AS

BEGIN  
--remove letters
with digits as 
(
select 0 as valid, cast('0' as nvarchar) as v
union all
select c.valid+1 as valid, cast(c.valid+1 as nvarchar) as v  from digits c
   where c.valid <9)
   , length as 
   (
   select 0 as l,cast('' as nvarchar) as s 
   union all
   select c.l+1, coalesce(a.v,'') as s  from length c
   outer apply 
   (select v from digits where v =SUBSTRING(@Acode,l+1,1))a
   where c.l <LEN(@Acode))
   , output as
   (
   select 0 as p, cast('' as nvarchar(max)) as str
   union all
   select p+1 as p, isnull(str,'') +s as str from length l join output o on o.p=l.l
   )

   select @ACode =str from output
   where p = (select max(p) from output)
 
--calculate check digit
   RETURN (10 - (CAST(SUBSTRING(@ACode, 1, 1) AS INTEGER)
                   + 3* CAST(SUBSTRING(@ACode, 2, 1) AS INTEGER)
                   + CAST(SUBSTRING(@ACode, 3, 1) AS INTEGER)
                   + 3* CAST(SUBSTRING(@ACode, 4, 1) AS INTEGER)
                   + CAST(SUBSTRING(@ACode, 5, 1) AS INTEGER)
                   + 3* CAST(SUBSTRING(@ACode, 6, 1) AS INTEGER)
                   + CAST(SUBSTRING(@ACode, 7, 1) AS INTEGER)
                   + 3* CAST(SUBSTRING(@ACode, 8, 1) AS INTEGER)
                   + CAST(SUBSTRING(@ACode, 9, 1) AS INTEGER)
                   + 3* CAST(SUBSTRING(@ACode, 10, 1) AS INTEGER)
                   + CAST(SUBSTRING(@ACode, 11, 1) AS INTEGER)
                   + 3* CAST(SUBSTRING(@ACode, 12, 1) AS INTEGER)
                  )%10
            )%10
 END;

>Solution :

you can use string concatenation with the + operator

But you need to convert the existing result of the expression from numeric to string first before you can concatenate

Change the RETURN to

RETURN @ACode 
+      CAST (
             (10 - (CAST(SUBSTRING(@ACode, 1, 1) AS INTEGER)
               + 3* CAST(SUBSTRING(@ACode, 2, 1) AS INTEGER)
               + CAST(SUBSTRING(@ACode, 3, 1) AS INTEGER)
               + 3* CAST(SUBSTRING(@ACode, 4, 1) AS INTEGER)
               + CAST(SUBSTRING(@ACode, 5, 1) AS INTEGER)
               + 3* CAST(SUBSTRING(@ACode, 6, 1) AS INTEGER)
               + CAST(SUBSTRING(@ACode, 7, 1) AS INTEGER)
               + 3* CAST(SUBSTRING(@ACode, 8, 1) AS INTEGER)
               + CAST(SUBSTRING(@ACode, 9, 1) AS INTEGER)
               + 3* CAST(SUBSTRING(@ACode, 10, 1) AS INTEGER)
               + CAST(SUBSTRING(@ACode, 11, 1) AS INTEGER)
               + 3* CAST(SUBSTRING(@ACode, 12, 1) AS INTEGER)
              )%10 
        )%10 
        AS VARCHAR(1000))