Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    US
    Posts
    2

    Question Unanswered: Simple procedure

    I am having trouble working out the logic for a procedure that forms part of a greater SQL package.
    PROCEDURE CreateAnAuthor (author .People.Name%TYPE, address people.Address%TYPE);
    this procedure is meant to search the Author table

    Create table AUTHOR(AuthorID, INT UNIQUE REFERENCES People(PersonID),
    PUblished INT DEFAULT 0,
    UnderReview INT DEFAULT 0);
    And add an author if there is no match. If necessary, it should also create a record in People table

    create table People (
    PersonID INT PRIMARY KEY,
    name VARCHAR 2(15),
    address VARCHAR2(15),
    UNIQUE (name, Address)
    );


    I feel I should be creating a recorfd and a cursor, but not sure if I am making a procedure more complex than it needs to be.
    Can anyone help?

    thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ....

    --well you can first check to see if it exist

    declare @cnt as char(1)
    set @cnt = (select count(field) from #auth where field = @param)


    if @cnt = '1'
    insert into #auth(field, field, field)
    select field, field, field

    insert into #people(field, field, field)
    select field, field, field
    else select 'ALREADY EXIST'--exit or what ever you need to do if exist

    --you will have to pass the parameters, but I assume you know how to
    --do that, if not let me know

Posting Permissions

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