Odtnhj

I want to know if a certain QuestionID and EmployeeID exist; if they do, they need to be inserted like here below (that's working fine).

But if they don't exist, I want a good error for the user, so that he knows that the QuestionID or the EmployeeID or both do not exist. Also maybe a rollback of the transaction? Now I can add every number, but the stored procedure still completed the commands...

I have this code (I'm using SQL Server):

CREATE PROCEDURE Contentment
 @employeeID INT,
 @questionid INT
AS
BEGIN
 IF EXISTS (SELECT questionid 
 FROM question 
 WHERE questionid = @questionid)
 IF EXISTS (SELECT employeeID 
 FROM employee 
 WHERE employeeid = @employeeID)
 BEGIN
 INSERT INTO contentment (employeeid, questionid, date, score, comment)
 VALUES (@employeeID, @questionid, null, null, null)
 END
 ELSE
 IF @employeeID = 0
 RAISERROR ('-certain error-', 16, 1, @employeeid) 

 IF @questionid = 0
 RAISERROR ('-certain error-', 16, 1, @questionid) 
END

I'd try to do it like this - check for the existence of both parameters in a single statement - if not, then provide an error:

CREATE PROCEDURE dbo.Contentment
 @employeeID INT,
 @questionid INT
AS
BEGIN
 -- check for *both* conditions at once
 IF EXISTS (SELECT * FROM dbo.question WHERE questionid = @questionid) AND
 EXISTS (SELECT * FROM dbo.employee WHERE employeeid = @employeeID)
 BEGIN
 -- if OK --> insert
 INSERT INTO dbo.contentment (employeeid, questionid, date, score, comment)
 VALUES (@employeeID, @questionid, null, null, null)
 END
 ELSE
 BEGIN
 -- if NOT OK --> provide error
 RAISERROR ('@EmployeeID or @QuestionID do not exist', 16, 1, null) 
 END
END

Also maybe a rollback of the transaction?

Since you're not really doing anything (no INSERT happening in either of the two ID's doesn't exist), there's really no transaction to roll back at this point....

you can use an error message parameter like that :

 CREATE PROC dbo.Usp_Contentment 
 AS 
begin
 declare @ErrMsg NVARCHAR(MAX) = ''
 IF not exists (select 1 from dbo.Question where QuestionId= @QuestionId)
 set @ErrMsg = 'Some msg'
 if not exists (select 1 from dbo.Employee where Employee Id= @EmployeeId)
 set @ErrMsg =@ErrMsg + 'some msg'

 IF @msg=''
 INSERT INTO dbo.contentment (employeeid, questionid, date, score, comment)
 VALUES (@employeeID, @questionid, null, null, null)
 else 
 RAISERROR(@ErrMsg,16,1)
end

don't use ROLLBACK because you handled the NULL Values

I would let the database do the work. Declare the table to have proper foreign key relationships:

alter table contentment add constraint fk_conententment_question
 foreign key (questionid) references question(questionid);

alter table contentment add constraint fk_conententment_employee
 foreign key (employeeid) references employee(employeeid);

Then write the procedure as:

CREATE PROCEDURE dbo.usp_Contentment (
 @employeeID INT,
 @questionid INT
) AS
BEGIN
 INSERT INTO dbo.contentment (employeeid, questionid, date, score, comment)
 VALUES (@employeeID, @questionid, null, null, null)
END; -- usp_Contentment

The database will generate an error that the foreign key relationship fails -- if there is no matching key.

This approach is much better than doing the test yourself for several reasons:

• The database ensures the data integrity, so you know the relationships are maintained regardless of where the data changes occur.


• The integrity applies to both inserts and updates. There is no need to put checks in multiple places.


• The integrity check is thread-safe, so if rows are being modified in the reference tables, the integrity check works.

If you really want a customized message, you can catch the error in a try/catch block and have a customized error.