Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Surrogate key as parameter in stored procedure?

    I have two tables:
    Code:
    countries(country_id integer, country_name string)
    authors(auth_id integer, country_id integer, auth_name string)
    ...Where "country_id" in the authors table refers to the same country_id in the countries table.

    I want a stored procedure to handle the insertion of new rows in the authors table. There are two methods of doing it:
    Code:
    1) CREATE PROCEDURE addAuthor( authorName, countryId )
    
    And
    
    2) CREATE PROCEDURE addAuthor( authorName, countryName )
    Now, I like #1 because the implementation is simple -- the calling code simply passes an author name, and a country id and an INSERT INTO statement is called with those parameters
    Code:
    INSERT INTO authors( @authorName, @countryId )
    I like #1, because it hides the surrogate "id" key from the application calling code. But on the downside, it has more overhead work, because you have to first a) verify a country with that name exists, and b) select that id into a variable.
    Code:
    DECLARE id INT;
    IF EXISTS (select * from countries where country_id = @countryId ) THEN
        SELECT country_id INTO id FROM countries WHERE country_name = @countryName;
    END IF;
    (Sorry I may have the SQL syntax wrong up there, but I was just trying to demonstrate the extra overhead involved).

    Which approach do you guys think is better?

  2. #2
    Join Date
    Jul 2007
    Posts
    96
    DECLARE @countryId Int;
    SET @countryId = (SELECT c.country_Id FROM countries c WHERE country_name = @countryName);

    IF (@countryId IS NOT NULL)
    -- Do insert
    ELSE
    RAISEERROR('invalid country', {severity}, {state});
    RETURN;

    Should the countries table be properly created you should have no problems using this approach. However, the presentation layer of the application usually sends the id value of the country directly to the business layer, which in turn sends it to the data layer, which would make you use the addAuthor(name, countryId) procedure all the time =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •