Storec Proc: INSERT Data If It Doesn't EXIST

beaudetious
beaudetious used Ask the Experts™
on
Hey Gang,

I have this stored procedure:

CREATE      PROCEDURE [sp_AddSubjectP2ep]
     @SubjectNumber varchar(6),
     @Subject varchar(50)

AS INSERT INTO [SubjectsP2ep]
      ( [SubjectNumber],
      [Subject])
 
VALUES
     (@SubjectNumber, @Subject)

A very simple stored procedure to add a row of data in a table that has three columns: SubjectID, SubjectNumber and Subject.  SubjectID is the primary key/identity column.

However, now the client would like to ensure they are not duplicating either the subject number or subject.  How can I rewrite this stored proc to check to see if either the subject number or subject currently exist and if so return a value like -1 or something.  Otherwise, insert the data and return a 1.

This is probably pretty normal behaviour (I hope).  Are there other approaches I should consider?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
INSERT INTO [SubjectsP2ep]
     ( [SubjectNumber],
     [Subject])

select (@SubjectNumber, @Subject)
where not exists (select subjectnumber from subjectsp2ep where subject = @subject) and not exists
(select subjectnumber from subjectsp2ep where subjectnumber = @subjectnumber)

Author

Commented:
Thanks for the response.  However, in my application I'd like to notify the user if the data was added or not.  So, if I didn't perform the insert I'd like to return a value to the routine that called the stored procedure.
add select @@ROWCOUNT after the insert.  It will be 0 if nothing was inserted 1 if there was.

Author

Commented:
Thanks.  I'll put it a test and see if it works.  Makes sense though.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial