March 8, 2021 at 2:23 pm
I created the procedure below, but the updates do not run, how do I run it all, could you help me?
ALTER PROCEDURE [dbo].[pp_updZeravalor]
as
begin
declare
@turnoid bigint,
@ProdutoID int,
@coletaid int,
@areaid int,
@sequencia int,
@prefixo int,
@Quantidade int=0,
@Contador int=0,
@valorUnitario int=0,
@creditos int=0,
@ValorMotorista int=0,
@recebido int=0,
@girosPagantes int=0
select top 1
@turnoid = t.TurnoID,
@ProdutoID = tq.ProdutoID,
@coletaid = col.coletaID,
@areaid = col.area,
@prefixo = col.prefixo,
@sequencia = t.Sequencia
from turnos t
inner join turnos_qtd tq on t.TurnoID = tq.TurnoID
left join coleta_detalhes col on t.Prefixo = col.prefixo
and t.LinhaID = col.linha
and t.Sequencia = col.sequencia
and t.NumSerie = col.equipamento
and t.DataFim = col.dataFim
and t.DataIni = col.dataIni
where 1 = 1
and t.DataIni between '2021-03-01' and '2021-03-02'
and t.MotoristaID in (103273, 103274, 63, 55015)
and tq.ProdutoID in (47, 3)
and (tq.Quantidade > 0 or col.girosPagantes >0)
if (@turnoid <> 0 or @turnoid is not null) and (@produtoid is not null or @produtoid <> 0)
begin
update Viagens_qtd set Quantidade = @Quantidade, Contador = @Contador where turnoid = @turnoid and ProdutoID = @produtoid
update ViagensEqvs set valorUnitario = @valorUnitario, creditos = @creditos where turnoid = @turnoid and ProdutoID = @produtoid
update turnos_qtd set Quantidade = @Quantidade, Contador = @Contador where turnoid = @turnoid and ProdutoID = @produtoid
update TurnosEqvs set valorUnitario = @valorUnitario, creditos = @creditos where turnoid = @turnoid and ProdutoID = @produtoid
update TurnosDinheiro set ValorMotorista = @ValorMotorista where turnoid = @turnoid
end
if @coletaid <> 0 or @coletaid is not null
begin
update coleta_detalhes set recebido = @recebido, girosPagantes = @girosPagantes where prefixo = @prefixo and sequencia = @sequencia and coletaID = @coletaid and area = @areaid
end
select
@turnoid ,
@produtoid,
@coletaid ,
@areaid ,
@prefixo,
@sequencia
end
GO
March 8, 2021 at 2:51 pm
Does your initial query return values? If not, the variables will not contain values.
Does it get inside the IF statement? If not, then the initial query is not producing a record. If it is, then the various update statements are not finding a record to update.
You can see what's being returned by adding a few "PRINT" statements to track flow, or configure the debugger and step through the code.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 8, 2021 at 3:01 pm
Add this statement immediately before the statement:
if (@turnoid <> 0 or @turnoid is not null)
That will help you see what results you are getting in the code and whether or not the code should enter the update block.
select @@rowcount as rowcount, @turnoid as TurnoID, @ProdutoID as ProdutoID,
@coletaid as coletaID, @areaid as area, @prefixo as prefixo, @sequencia as Sequencia
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 8, 2021 at 4:45 pm
So, I didn't really want to set up a procedure, I was patching it, using the select that is included in it, and I will also create a schedulle to run constantly.
what I need in reality is that every time I contain the select information, reset this data.
Could you help me write this script?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply