The logo, pretends to give the communication as the major role between Business and IT.This blog continues to post for Tech people. Today I post one more of the saga of SQL Tip and Tricks about a Store procedure (SP) with a cursor inside to update maintance contracts
--
-- Creates SP com o nome sp_update_onsite
--
create procedure [dbo].[sp_update_onsite]
as Begin
--
-- Inserts ticket in a table when the status is 'Closed'
-- I use an Insert with a Select
--
INSERT INTO [HEAT].[dbo].[Tickets] ([CallId] ,[CallType] ,[CriData] ,[Valor] ,[Saldo] ,[UIDMANUT])
select d.callid,c.calltype,c.closeddate, d.horas_gastas, d.horas_saldo
,d.uidmanut,c.custid
from detail d
inner join calllog c on c.callid=d.callid
inner join subset s on s.callid=c.callid
inner join config cfg on cfg.u_idreg=d.uidmanut
where (c.calltype='Onsite' or c.calltype='Packs') and c.callstatus='Closed' and c.actualiza<>'SIM'
and c.callid not in (select callid from tickets )
--
--
-- Cursor used to update
--
-- Declares the Cursor Cursor_Tickets
-- and Select where it runs the Cursor
DECLARE Cursor_tickets CURSOR for
SELECT t.CallId,c.custid,t.Valor,t.UIDMANUT
from tickets t
inner join calllog c on c.callid=t.callid
where t.[CallType]='Onsite' and c.actualiza <>'SIM'
--
-- Declares the variables to pass througth
--
Declare @Callid varchar(8)
declare @custid varchar(50)
declare @valor decimal(17,2)
declare @uidmanut varchar(25)
set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''
Open Cursor_tickets /* abrir o cursor */
fetch next from Cursor_tickets
into @callid,@custid,@valor,@uidmanut
while @@fetch_Status=0
begin
--- Updates hours spent
--- select @valor,@custid,@uidmanut (for Testing purposes)
update config set horas_gastas=isnull(@valor,0)+(isnull(horas_gastas,0)) ,horas_saldo=isnull(horas_saldo,0)-isnull(@valor,0)
where u_idreg=@uidmanut and custid=@custid
set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''
fetch next from Cursor_Tickets
into @callid,@custid,@valor,@uidmanut
end
close Cursor_tickets
Deallocate Cursor_tickets
--
-- In the end updates the table for not processing agains
--
update calllog set actualiza='SIM' where callid in (select callid from tickets) and actualiza<>'SIM'
end
