Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Unhappy Unanswered: little help on delete stored procedure

    I created a insert stored procedure but It was not working correctly
    Could you correct the code?
    I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table

    Please help!

    ************************************************** ***
    My contrat DDL is follows

    create table contract(
    contractNum int identity(1,1) primary key,
    contractDate smalldatetime not null,
    tuition money not null,
    studentId char(4) not null foreign key references student (studentId),
    contactId int not null foreign key references contact (contactId)
    );

    ************************************************** ***
    My insert stored procedure is follows

    create proc sp_insert_new_contract
    ( @contractDate [smalldatetime],
    @tuition [money],
    @studentId [char],
    @contactId [int])
    as
    if exists (select s.studentId, c.contactId
    from student s, contact c
    where @contactId = contactId
    and @studentId = studentId)


    begin
    insert into contract
    ([contractDate],
    [tuition],
    [studentId],
    [contactId])
    values
    (@contractDate,
    @tuition,
    @studentId,
    @contactId)

    end
    else
    print 'studentId and contactId are not valid, please try another studnetId and contactId'
    go

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Syntactically, it should be:

    create proc sp_insert_new_contract
    ( @contractDate [smalldatetime],
    @tuition [money],
    @studentId [char] (4),
    @contactId [int])
    as
    if exists (select s.studentId, c.contactId
    from student s, contact c
    where @contactId = contactId
    and @studentId = s.studentId)
    ...

    But logically, you're also missing a validity of JUST a student id:

    declare @student_check char(4)
    select @student_check = max(studentid) from student where studentid = @studentid
    if @student_check is null begin
    print 'Invalid studentid specified'
    return (1)
    end

    if exists (select 1 from contact
    where @contactId = contactId
    and @studentId = @studentId) begin
    print 'Specified studentid/contractid already exist in the table!'
    return (1)
    end
    ...
    After then you can go on with the rest of your procedure.
    Last edited by rdjabarov; 07-14-04 at 22:26.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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