O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required