Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

8.4. Declarations, Not Functions, Not Procedures

After I have given all of these algorithms, you should not use them in procedural code in your schema. Convert them to constraints instead. This is an example of thinking in sets and not procedures. In a posting on www.swug.org, a regular contributor posted a Transact-SQL function that calculates the checksum digit of a standard, 13-digit bar code. The rules are simple:

1. Sum each digit in an odd position to get S1.

2. Sum each digit in an even position to get S2.

The formula is ABS (MOD (S1-S2), 10) for the bar code checksum digit. Here is the author’s suggested function code, translated from T-SQL into Standard SQL/PSM:

CREATE FUNCTION BarcodeCheckSum(IN my_barcode CHAR(12)) RETURNS INTEGER LANGUAGE ...