check if two values already exists in table stored procedure-CodePudding

So I'm trying to create a stored procedure that takes two values, but it has to check if there already exists a row with these two values in it, but how?

CREATE PROCEDURE `ChangeDashboardContent`(TestSuiteCollectionId varchar(45), Project varchar(45))
BEGIN
  /*Here it should check if these two values already exsists*/
  IF select * from dashboard_hidden where TestSuiteCollectionId = 
  TestSuiteCollectionId  and Project  = Project then end if and do nothing because they are already there.
  ELSE /*if this row doesn't exists implement it*/
  INSERT INTO dashboard_hidden(`TestSuiteCollectionId`,`Project`) 
  VALUES (TestSuiteCollectionId, Project);
  END IF;
END

CodePudding user response：

How about using IF NOT EXISTS

CREATE PROCEDURE `ChangeDashboardContent`(TestSuiteCollectionId varchar(45), Project varchar(45))
BEGIN
  /*Here it should check if these two values already exsists*/
  IF NOT EXISTS (select * from dashboard_hidden where TestSuiteCollectionId = 
  TestSuiteCollectionId  and Project  = Project)
  BEGIN
     INSERT INTO dashboard_hidden(`TestSuiteCollectionId`,`Project`) 
     VALUES (TestSuiteCollectionId, Project);
  END 
 
END

CodePudding user response：

you can use the Exists operator for this case

IF NOT EXISTS (SELECT 1 FROM dashboard_hidden where TestSuiteCollectionId = 
  TestSuiteCollectionId  and Project  = Project)
BEGIN

INSERT INTO dashboard_hidden(`TestSuiteCollectionId`,`Project`) 
  VALUES (TestSuiteCollectionId, Project);

END

The above will complete your requirement. However if you have the condition like this in your table as two columns should have unique values the you might consider adding the unique key constraint. SQL will then not allow any insert if both values already exists in together, then you don't need to check for If Exist in the stored procedure.

ALTER TABLE dashboard_hidden
ADD CONSTRAINT UQ_SuiteCollection_Project UNIQUE (TestSuiteCollectionId,Project);