#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0

    Stored procedure help


    I have the following stored procedure that should check if the artist being added is in the Allowed Nationality table but it doesnt work it inserts an artist weather it is in the allowe nationality table or not, can anyone tell em what i ahve done wrong?

    DELIMITER //
    CREATE PROCEDURE `InsertNewArtistCheck`
    (IN newLastName Char(25),
    IN newFirstName Char(25),
    IN newNationality Char(30),
    IN newDateOfBirth Numeric(4),
    IN newDateDeceased Numeric(4))
    BEGIN
    DECLARE varRowCount Int;
    SELECT Nation INTO varRowCount
    FROM ALLOWED_NATIONALITY
    WHERE Nation = newNationality;
    IF (varRowCount < 0)
    THEN
    ROLLBACK;
    SELECT 'Nationality Not Allowed' AS ErrorMessage;
    END IF;
    INSERT INTO ARTIST
    (LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (newLastName, newFirstName, newNationality, newDateOfBirth,
    newDateDeceased);
    SELECT 'New artist data added to database' AS InsertStatus;
    END//
    DELIMITER ;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep Power
    9
    it does your instruction...

    INSERT INTO ARTIST
    (LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (newLastName, newFirstName, newNationality, newDateOfBirth,
    newDateDeceased);
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by gk53
    it does your instruction...

    INSERT INTO ARTIST
    (LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (newLastName, newFirstName, newNationality, newDateOfBirth,
    newDateDeceased);
    Yes it does this, but it should only insert if the artists nationality is one of 5 allowed nationalities from the ALLOWED_NATIONALITY table, it currently inserts weather the artists nationality is allowed or not
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    I am not up on stored procedures but from what I can see if the Nationality is not valid (by the way do you want/need to check less then OR equal to 0 here?) then you set up an error message, along with a ROLLBACK (though I am wondering why you feel you need to do that at this point as nothing has yet happened beyond a SELECT). Once the IF condition has been evaluated, then regardless of the outcome of that you will, unilaterally, do the INSERT command.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    pardon me for sticking my nose in here, but you don't really want a stored procedure for this

    a simple foreign key will do exactly the same, with no code whatsoever on your part

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by SimonJM
    I am not up on stored procedures but from what I can see if the Nationality is not valid (by the way do you want/need to check less then OR equal to 0 here?) then you set up an error message, along with a ROLLBACK (though I am wondering why you feel you need to do that at this point as nothing has yet happened beyond a SELECT). Once the IF condition has been evaluated, then regardless of the outcome of that you will, unilaterally, do the INSERT command.
    Thank you very much i have sorted it now

IMN logo majestic logo threadwatch logo seochat tools logo