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'
goSyntactically, 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.
