I have a procedure in my BD in which it records material in the database, so far, but in the registration procedure I need to update another table assigning the amount of material to a column x, y depending on the type of material, I saved and executed the procedure.
The relation is as follows: I will record material in the "material" table, and in the procedure it takes the material type by the parameter "typeMaterial", and will add in the column of the table "totals" the value according to the material or , if in the material register the type of the material is Paper (which will be the @typeMaterial) it would add with the current value plus the current collection in the column totalPapel of the table totColetas, however always when executing I return the result "0 rows affected ". Could someone give me a light or some easier and better way of doing this (if mine is bad)?Follow the proc code:
create proc cadMat /*Cadastra o material no banco de dados Ok!(sistema)*/
@cpfColetor char(12),
@nomeMaterial varchar(30),
@tipoMaterial varchar(8),
@zonaMaterial varchar(10),
@qtdeMaterial float,
@dataRecebimentoMaterial datetime
as
begin
insert into material values(@nomeMaterial,@tipoMaterial,@zonaMaterial,@qtdeMaterial,@dataRecebimentoMaterial,@cpfColetor,'')
update coletor
set qtdeMatCol +=@qtdeMaterial where cpfCol = @cpfColetor
update material
set material.nomeColMat = coletor.nomeCol from material join coletor on coletor.cpfCol = material.cpfColMat where material.cpfColMat = @cpfColetor
update totColetas
set totalPapel += @qtdeMaterial
end