Create SP with Cursor

These is my first post in the New Year of 2009, because i had lot's of thing in my professional side, you already notice the change in the logo and in the name of the Blog, i had register the domain www.ITTechBuZ.com e novo logo.
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

No response to “Create SP with Cursor”

Post a Comment