SQL Server - Doubt procedure and relationship between tables

1

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
    
asked by anonymous 23.05.2015 / 03:36

0 answers