Cfrgtkky

I use SQL Server.

I want to write a stored procedure that looks if a questionid and employeeid exists (questionid is in the table question, same for employeeid, is in the table employee) AND looks if they not already existing in the table (you don't get a duplicate in the table contentment). I want raiserror's for the user if so.

In my case it is possible to have a duplicate but not on the same DATE!

Contentment table has columns:

employeeid, questionid, date, score

employeeid, questionid, date make up the primary key.

So I want something like this:

1,1, 18-11-2018, null

1,1, 19-11-2018, null

and not something like this:

1,1, 18-11-2018, null

1,1, 18-11-2018, null

I already made something but it is not working (1-1-1900 is a standard date, because it is primary key it needs to be inserted, score is not inserted because the user needs to do this):

    @employeeid int,

    @questionid int

as 

begin

    if exists (select * from question where questionid = @questionid)

       and exists (select * from employee where employeeid= @employeeid)

    begin 

        insert into contentment (employeeid, questionid, date, score)

        values (@employeeid, @questionid, '1-1-1900', null)

    end



    if (select count(*) 

        from contentment

        where employeeid = @employeeid 

          and questionid = @questionid 

          and date = date) = 0

        raiserror ('@employeeid or @questionid already existing', 16, 1)    

    else

        raiserror ('@employeeid or @questionid are not existing', 16, 1, null)          

    end

If you want to date validation you need to provide @date as well. I have created a sample stored procedure with detail that you provide:

DROP PROCEDURE P_ContentmentInsert

GO

CREATE PROCEDURE P_ContentmentInsert

    @employeeid int,

    @questionid int,

    @date       DATE

AS

BEGIN

    --Check if exists Employee, Question and Contentment

    DECLARE @IsEmployeeExists       INT=ISNULL((SELECT COUNT(1) FROM employee WHERE employeeid= @employeeid),0)

    DECLARE @IsQuestionExists       INT=ISNULL((SELECT COUNT(1) FROM question WHERE questionid = @questionid),0)

    DECLARE @IsContentmentExists    INT=ISNULL((SELECT COUNT(1) FROM contentment WHERE questionid = @questionid and employeeid= @employeeid and [date]=@date),0)

    DECLARE @ErrorMessage           VARCHAR(1000)=''



    --If one of the validation not passed give error message

    IF (@IsEmployeeExists=0 OR @IsQuestionExists=0 OR @IsContentmentExists=0)

    BEGIN

        IF @IsEmployeeExists=0

                SET @ErrorMessage='-EmployeeId Not exists'

        IF @IsQuestionExists=0

                SET @ErrorMessage=@ErrorMessage+'-QuesitonId Not exists'

        IF @IsContentmentExists=0

                SET @ErrorMessage=@ErrorMessage+'-Contentment already exists'

        RETURN

    END



    --If there is no problem insert it.

    IF @IsEmployeeExists>0 and @IsQuestionExists>0 and @IsContentmentExists>0

    BEGIN

        INSERT INTO contentment (employeeid, questionid, date, score)

        VALUES (@employeeid, @questionid, @date, null)

    END



END