Good day,
Luckily i run a query just to view the DDL for the GenerateSequenceNo
here is the code...
ALTER PROCEDURE [dbo].[spQuery_GetSequenceNo1]
---spQuery_GetSequenceNo '502147940', '171156'
@intCustomerNo VARCHAR(9) = NULL
,@ParamAmount VARCHAR(50) = NULL
,@PaymentDate DATETIME
--------------------------
/*
##_BEGIN
CREATED BY: JANE DIAZ
##_END
*/
--------------------------
AS
CREATE TABLE #tempSeqA
(
intCol INT
,intCustNo INT
,intSeqNo INT
,dblProduct FLOAT
)
DECLARE
@counter INT
,@charCustno INT
,@charSeqno INT
,@SumofProduct INT
,@Modulo INT
,@Seq2 varchar(10)
SET @counter = 0
/*
GET PRODUCT OF SEQUENCE NO AND CUSTOMER NO
*/
WHILE @counter < 9
BEGIN
SET @counter = @counter + 1
SET @charCustno = (SELECT SUBSTRING(CONVERT(VARCHAR,@intCustomerNo),@counter,1))
SET @charSeqno = (SELECT TOP 1 intValue FROM tblSequenceA WHERE intCode = @counter)
INSERT INTO #tempSeqA
VALUES(
@counter
,@charCustno
,@charSeqno
,@charCustno * @charSeqno
)
END
--SELECT * FROM #tempSeqA
/*
GET SUM OF THE PRODUCTS
*/
SELECT @SumofProduct = SUM(dblProduct)
FROM #tempSeqA
--SELECT @SumofProduct
/*
COMPUTE THE MODULO
-- if remainder is 10 or 0 then value is "0" , then multiply to "9" and get the
last digit. (last digit is the CHECK DIGIT)
*/
SELECT @Modulo = (CASE WHEN (@SumofProduct % 11) = 10 THEN 0
ELSE CASE WHEN (@SumofProduct % 11) = 0 THEN 0
ELSE (@SumofProduct % 11) END
END ) * 9
--Declare @tablevar table(colValue varchar(10))
Declare @tablevar table(colValue varchar(11)) --Add 1 char for check digit C of MDDYC99999; herbert k. ; Aug. 2, 2012
insert into @tablevar
EXEC [spQuery_GetSequenceNo2]
@intParamAmount = @ParamAmount
,@dtePaymentDate = @PaymentDate
SELECT SEQ_NO = SUBSTRING(CONVERT(VARCHAR,@Modulo),LEN(@Modulo),1) + (select top 1 * from @tablevar)
DROP TABLE #tempSeqA
------------------------------------------------------------------------------------------