CODEWITHSUNDEEP
sql-servercursor
Jędrzej
Jędrzej

Reputation: 49

SQL Server, cursor never ending

My task is to make it impossible to add a row that contains wrong form of certain attribute called pesel. Firstly I thought I should write a trigger and it worked fine but I was told to change it to cursor because when user want to add many values at once insert will stop at first wrong pesel and it won't check the others. So far I came up with this but it seems like it never ends after I execute it. 

DECLARE cursor_pesel CURSOR FOR 
     SELECT pesel FROM person;

DECLARE @pesel CHAR(11)
DECLARE @WEIGHTS as table (position INT IDENTITY(1,1), weight TINYINT)

INSERT INTO @WEIGHTS 
VALUES (1), (3), (7), (9), (1), (3), (7), (9), (1), (3), (1)

OPEN cursor_pesel
FETCH NEXT FROM cursor_pesel INTO @pesel

WHILE @@FETCH_STATUS = 0 
BEGIN
    IF (LEN(@pesel) != 11 OR (ISNUMERIC(@pesel) = 0))
        RAISERROR('BAD FORM OF PESEL',1,1)

    IF (SELECT SUM(CONVERT(INT, SUBSTRING(@pesel, position,1)) * weight) % 10
        FROM @WEIGHTS) != 0
        RAISERROR('BAD FORM OF PESEL',1,1)
END

CLOSE cursor_pesel

Upvotes: 0

Views: 298

Answers (1)

McNets
McNets

Reputation: 10807

You forgot to fetch next records:

FETCH NEXT FROM cursor_pesel INTO @pesel


DECLARE cursor_pesel CURSOR
FOR SELECT pesel FROM person;
DECLARE @pesel CHAR(11)
DECLARE @WEIGHTS as table (position INT IDENTITY(1,1), weight TINYINT)
INSERT INTO @WEIGHTS VALUES (1),(3),(7),(9),(1),(3),(7),(9),(1),(3),(1)

OPEN cursor_pesel

FETCH NEXT FROM cursor_pesel INTO @pesel

WHILE @@FETCH_STATUS=0 
BEGIN

    IF (LEN(@pesel)!=11 OR (ISNUMERIC(@pesel)=0))
        RAISERROR('BAD FORM OF PESEL',1,1)

    IF (SELECT SUM(CONVERT(INT, SUBSTRING(@pesel, position,1))*weight)%10
        FROM @WEIGHTS)!=0
        RAISERROR('BAD FORM OF PESEL',1,1)

    FETCH NEXT FROM cursor_pesel INTO @pesel   <--------
END

CLOSE cursor_pesel

Upvotes: 2

Related Questions