May 26, 2010 at 4:42 pm
Hello community
I was very curious about the article :
Cursors Be Gone!
By Gaby Abed, 2010/06/18 (first published: 2008/12/24)
Also, I have the following stored procedure that use inside a cursor and one table-valued functions named dbo.fnSaldoContaML:
USE [MYDATABASE]
GO
/****** Object: StoredProcedure [dbo].[spSLDTerceirosSNC] Script Date: 05/26/2010 23:16:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spSLDTerceiros 2009, '21101', 2010, 0, '20100101'
ALTER PROCEDURE [dbo].[spSLDTerceirosSNC]
@anofiltro int,
@contafiltro varchar(20),
@anomov int,
@mesmov int,
@data datetime
AS
BEGIN
SET XACT_ABORT ON
DECLARE @conta varchar(20), @subconta varchar(30), @deb numeric(18,3), @cre numeric(18,3),
@edeb numeric(18,3), @ecred numeric(18,3)
DECLARE @dostamp varchar(25), @dilno bigint
DECLARE @totdebito numeric(18,3), @totcredito numeric(18,3)
DECLARE @saldodeb numeric(18,3), @saldocred numeric(18,3)
DECLARE @lancdeb numeric(18,3), @lanccred numeric(18,3)
DECLARE @somadeb numeric(18,3), @somacred numeric(18,3)
DECLARE @ultlordem int
--1º Actualiza a PC e poe Subconta = 1 baseando-se no ano anterior-----------
UPDATE pc SET u_subconta = 1
FROM pc
INNER JOIN
(SELECT conta, u_subconta FROM pc WHERE pc.ano = (@anofiltro)) pcanterior ON
pc.conta = pcanterior.conta
WHERE pc.ano = (@anofiltro + 1) AND pcanterior.u_subconta = 1
------------------------------------------------------------------------------
DECLARE pcsa CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT pcsa.conta
FROM pcsa INNER JOIN pc ON pcsa.conta = pc.conta
WHERE pcsa.ano = @anofiltro AND (pc.u_subconta = 1)
and pcsa.conta LIKE @contafiltro + '%'
GROUP BY pcsa.conta
OPEN pcsa
FETCH NEXT FROM pcsa INTO @conta
BEGIN TRAN
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT g.lancar FROM gt g WHERE contapoc = @conta AND g.evalor <> 0) = 1
BEGIN
-- NOVO dostamp
SET @dostamp = LEFT(NEWID(), 23)
-- Número de lançamento no diário
SELECT @dilno = (MAX(dilno) + 1) FROM do WHERE dino = 10
-- OBTER OS TOTAIS DE DÉBITO E CRÉDITO
SELECT @totdebito = SUM(saldoedeb), @totcredito = SUM(saldoecre)
FROM dbo.fnSaldoContaML(@conta, @anofiltro, @anomov, @mesmov)
--PRINT CAST(@totdebito AS varchar) + ' ' + CAST(@totcredito AS varchar)
IF NOT (@totdebito IS NULL) AND NOT (@totcredito IS NULL)
BEGIN
IF @totdebito > @totcredito
BEGIN
SET @saldocred = 0
SET @saldodeb = (@totdebito - @totcredito)
SET @lanccred = (@totdebito - @totcredito)
SET @lancdeb = 0
END
ELSE
BEGIN
SET @saldodeb = 0
SET @saldocred = (@totcredito - @totdebito)
SET @lancdeb = (@totcredito - @totdebito)
SET @lanccred = 0
END
--PRINT @conta
--PRINT 'Saldodeb: ' + CAST(@saldodeb AS varchar) + ' SaldoCred: ' + CAST(@saldocred AS varchar)
INSERT INTO pcsa (pcsastamp, conta, ano, mes, deb, cre, edeb, ecre, u_subconta)
SELECT stamp, /*conta*/dbo.fnContaSNC(@conta, @anomov) + '-' + subconta, @anomov, @mesmov, saldodeb, saldocre, saldoedeb, saldoecre, subconta
FROM dbo.fnSaldoContaML(@conta, @anofiltro, @anomov, @mesmov)
IF @totdebito <> @totcredito
BEGIN
-- Inversão do saldo
INSERT INTO ml (mlstamp, dinome, dilno, docnome,
data, mes, dia, conta, descricao,
deb,
cre,
edeb,
ecre,
dino, descritivo, doctipo,
lordem, origem, u_subconta, dostamp)
VALUES (LEFT(NEWID(), 23), '10 - Abertura', @dilno, 'SI - SALDO DE ABERTURA',
@data, 0, 1, dbo.fnContaSNC(@conta, @anomov), 'Balanceamento de abertura',
((@lanccred * -1) * 200.482),
((@lancdeb * -1) * 200.482),
(@lanccred * -1),
(@lancdeb * -1),
10, 'Balanceamento de abertura', 81,
1000, 'DO', '', @dostamp)
END
INSERT INTO ml (mlstamp, dinome, dilno, docnome,
data, mes, dia, conta, descricao,
deb,
cre,
edeb,
ecre,
dino, descritivo, doctipo,
lordem, origem, u_subconta, dostamp)
SELECT LEFT(NEWID(), 23), '10 - Abertura', @dilno, 'SI - SALDO DE ABERTURA',
@data, 0, 1, dbo.fnContaSNC(contasimples, @anomov) as conta, LEFT(pc.descricao, 35),
(CASE WHEN @saldodeb > 0 THEN
(CASE WHEN saldodeb > 0 THEN saldodeb WHEN saldocre > 0 THEN (saldocre * -1) END) ELSE 0 END) AS deb,
(CASE WHEN @saldocred > 0 THEN
(CASE WHEN saldocre > 0 THEN saldocre WHEN saldodeb > 0 THEN (saldodeb * -1) END) ELSE 0 END) AS cre,
(CASE WHEN @saldodeb > 0 THEN
(CASE WHEN saldoedeb > 0 THEN saldoedeb WHEN saldoecre > 0 THEN (saldoecre * -1) END) ELSE 0 END) AS edeb,
(CASE WHEN @saldocred > 0 THEN
(CASE WHEN saldoecre > 0 THEN saldoecre WHEN saldoedeb > 0 THEN (saldoedeb * -1) END) ELSE 0 END) AS ecre,
10 as dino, pc.descricao, 81,
((ROW_NUMBER() OVER (ORDER BY u_subconta) + 2) * 1000) AS lordem, 'DO',
subconta AS u_subconta, @dostamp
FROM dbo.fnSaldoContaML(@conta, @anofiltro, @anomov, @mesmov) s INNER JOIN pc ON
s.contasimples = pc.conta AND pc.ano = @anofiltro
SELECT @ultlordem = MAX(lordem) FROM ml WHERE dostamp = @dostamp
INSERT INTO ml (mlstamp, dinome, dilno, docnome,
data, mes, dia, conta, descricao,
deb,
cre,
edeb,
ecre,
dino, descritivo, doctipo,
lordem, origem, u_subconta, dostamp)
VALUES (LEFT(NEWID(), 23), '10 - Abertura', @dilno, 'SI - SALDO DE ABERTURA',
@data, 0, 1, dbo.fnContaSNC('12909', @anomov), 'Balanceamento de abertura',
((@lancdeb) * 200.482),
((@lanccred) * 200.482),
(@lancdeb),
(@lanccred),
10, 'Balanceamento de abertura', 81,
@ultlordem + 1, 'DO', '', @dostamp)
INSERT INTO ml (mlstamp, dinome, dilno, docnome,
data, mes, dia, conta, descricao,
deb,
cre,
edeb,
ecre,
dino, descritivo, doctipo,
lordem, origem, u_subconta, dostamp)
VALUES (LEFT(NEWID(), 23), '10 - Abertura', @dilno, 'SI - SALDO DE ABERTURA',
@data, 0, 1, dbo.fnContaSNC('12909', @anomov), 'Balanceamento de abertura',
((@lanccred) * 200.482),
((@lancdeb) * 200.482),
(@lanccred),
(@lancdeb),
10, 'Balanceamento de abertura', 81,
@ultlordem + 2, 'DO', '', @dostamp)
SELECT @somadeb = SUM(edeb), @somacred = SUM(ecre)
FROM ml
WHERE dostamp = @dostamp
INSERT INTO do (dostamp, dinome, dilno, docnome, data, mes, dia,
dino, doctipo, edebfin, ecrefin, debfin, crefin)
VALUES (@dostamp, '10 - Abertura', @dilno, 'SI - SALDO DE ABERTURA', @data, 0, DAY(@data),
10, 81, @somadeb, @somacred, ROUND(@somadeb * 200.482, 2),
ROUND(@somacred * 200.482, 2))
END --IF NOT (@totdebito IS NULL) AND NOT (@totcredito IS NULL)
END
FETCH NEXT FROM pcsa INTO @conta
END
COMMIT TRAN
CLOSE pcsa
DEALLOCATE pcsa
DELETE FROM pcsa WHERE u_subconta <> ''
END
My curiosity is how can use the same post technique to replace my current cursor code ??
Someone could write the same code using the example of this article, because I think it´s much easier to understand and this article show the better performance on this tecnique.
Many thanks
Luis Santos
May 26, 2010 at 5:54 pm
You might want to check out the articles by RBarryYoung on 15 Ways to Lose Your Cursor[/url] - he walks you through it step by step.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply