I am trying to create a procedure to validate if a record is already created, what I think I do is that I declare a res variable and then do an if exist to determine if the record exists and then if it exists or not, I perform its respective insert. But when creating the procedure it tells me that the res variable has not been declared.
But when I run it, it returns null.
My procedure is as follows:
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddRevisionRamos`(_fecha nvarchar(45), _evaluador nvarchar(45), _codigo nvarchar(3), _nramo nvarchar(1), _ma nvarchar(1), _pc nvarchar(1), _tt nvarchar(1), _tr nvarchar(1), _td nvarchar(1), _tc nvarchar(1), _ca nvarchar(1), _fito nvarchar(1), _sc nvarchar(1), _cap nvarchar(1), _observaciones nvarchar(45), _finca nvarchar(45), _semana nvarchar(2), _operario nvarchar(45), _supervisor nvarchar(45), _bloque nvarchar(3), _idApp nvarchar(18))
BEGIN
#select @res;
select @res = if (EXISTS (
select * from reg_evaluacion where _idApp = @idApp),1,0);
if @res = 1 then
INSERT INTO `revisionramos`.`reg_evaluacion` (`FECHA`, `EVALUADOR`, `CODIGO`, `NRAMO`, `MA`, `PC`, `TT`, `TR`, `TD`, `TC`, `CA`, `FITO`, `SC`, `CAP`, `OBSERVACIONES`, `FINCA`, `SEMANA`, `OPERARIO`, `SUPERVISOR`, `BLOQUE`)
VALUES (_fecha, _evaluador, _codigo, _nramo, _ma, _pc, _tt, _tr, _td, _tc, _ca, _fito, _sc, _cap, _observaciones, _finca, _semana, _operario, _supervisor, _bloque);
end if;
END
and these are the fields of my table:
Well let's go by part,
@idApp
I don't see it exists in your current context; except in the same connection you are declaring it from outside the call to its SP ; I see it exists_idApp
and with it I propose the following :Change:
by:
I hope it is useful for you.
Don't confuse session variables (those that start with an at sign) with local ones (either from
DECLARE
or passed by parameters).