dimanche 25 juin 2017

ABS ( CHECKSUM ( NEWID () ) ) % 4 generates unexpected values

the following transact-sql code works properly. it fills up the table with four integers ( 0, 1, 2, 3 ) as expected.

CREATE TABLE [TBL_INTEGER] (
    [ID] INTEGER IDENTITY ( 1, 1 ) PRIMARY KEY,
    [NUMBER] INTEGER NOT NULL
)

DECLARE @MAX INTEGER
SELECT @MAX = 1000

WHILE ( 0 < @MAX ) BEGIN
    INSERT [TBL_INTEGER] ( [NUMBER] ) SELECT ABS ( CHECKSUM ( NEWID () ) ) % 4
    SELECT @MAX = @MAX - 1
END

and the following code does not. it fails generating the 'Cannot insert the value NULL into column 'NUMBER' error.

CREATE TABLE [TBL_INTEGER] (
    [ID] INTEGER IDENTITY ( 1, 1 ) PRIMARY KEY,
    [NUMBER] INTEGER NOT NULL
)

DECLARE @MAX INTEGER
SELECT @MAX = 1000

WHILE ( 0 < @MAX ) BEGIN
    INSERT [TBL_INTEGER] ( [NUMBER] )
        SELECT
            CASE ABS ( CHECKSUM ( NEWID () ) ) % 4
                WHEN 0 THEN 0
                WHEN 1 THEN 1
                WHEN 2 THEN 2
                WHEN 3 THEN 3
            END
    SELECT @MAX = @MAX - 1
END

if i add

ELSE 99

the code does not fail. but one third of the inserted rows contain the value 99.

is there an explanation ?

thank you in advance !

av




Aucun commentaire:

Enregistrer un commentaire