Click here to Skip to main content
Click here to Skip to main content

Check Digit Vertical and Horizontal

, 6 May 2013
Rate this:
Please Sign up or sign in to vote.
This tip explains how to calculate the Check Digit Vertical (CDV) and Horizontal (CDH) by SQL Server.

Introduction

This tip explains how to calculate the Check Digit Vertical (CDV) and Horizontal (CDH) by SQL Server.

Using the Code

For this example, we will use a temporary table so it can be implemented and tested in any SQL.

CREATE TABLE #TableTemporal (Field1 int, Field2 varchar(50), CDH int) 

Each insert must calculate the CDH (CHECKSUM).

INSERT INTO #TableTemporal VALUES (1,'First field', BINARY_CHECKSUM(1,'First field'))
INSERT INTO #TableTemporal VALUES (2,'Second field', BINARY_CHECKSUM(2,'Second field'))
INSERT INTO #TableTemporal VALUES (3,'Third field', BINARY_CHECKSUM(3,'Third field'))
INSERT INTO #TableTemporal VALUES (4,'Fourth field', BINARY_CHECKSUM(4,'Fourth field')) 

We control everything is inserted and the CDH calculated.

 select * from #TableTemporal 

Now calculate the Check Digit Vertical:

SELECT  CHECKSUM_AGG(CDH) as CDV
FROM    #TableTemporal 

In the next example, we will change records and see how they change the CDH and CDV.

We modify a record:

update #TableTemporal
set Field2 = 'First field modified'
where Field1 = 1 

To modify a record should be re recalculate the CDH (CHECKSUM).

update #TableTemporal
set CDH = BINARY_CHECKSUM(Field1, Field2)
where Field1 = 1 

We compared the CDH of the first record with the previous and see that it has changed.

select * from #TableTemporal

Calculation on the column CDH CDV after modification:

SELECT  CHECKSUM_AGG(CDH) as CDV
FROM    #TableTemporal 


Now we will verify that the digits are correct Horizontal Verifiers and no record has been modified outside of our system.

If everything is correct, it should not return any record.

SELECT *
FROM #TableTemporal
WHERE CDH <> BINARY_CHECKSUM(Field1, Field2) 

Then we will simulate the modified record outside our system.

We again modify the first record but without updating your CDH.

update #TableTemporal
set Field2 = 'First field modified 2'
where Field1 = 1 

If everything is correct, you must return the first record.

SELECT *
FROM #TableTemporal
WHERE CDH <> BINARY_CHECKSUM(Field1, Field2) 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Hernán Hegykozi
Program Manager
Argentina Argentina
No Biography provided
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 7 May 2013
Article Copyright 2013 by Hernán Hegykozi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid