Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using IF condition on Cursor Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 9:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:11 PM
Points: 266, Visits: 560
Hello Carlos

Your Portuguese is OK, Carlos.
For everybody to answer me,i solve my problem after some changes on my original cursor and i test ir with 2 "referencia":

DECLARE cur1 CURSOR LOCAL FORWARD_ONLY FOR
SELECT
referencia, TAMANHO, LEFT(NEWID(),23) as 'sgtstamp',
min(QTD) AS 'SORTIMENTO',
ROW_NUMBER() OVER(PARTITION BY referencia ORDER BY referencia,tamanho ASC) AS 'Rownumb'
FROM arttamcor WHERE referencia IN ('29913','30110') <-- 2 articles
GROUP BY referencia, TAMANHO
ORDER BY referencia,tamanho,rownumb


-- Cursor Variables --
DECLARE @ref VARCHAR(18)
DECLARE @TAMANHO VARCHAR(25)
DECLARE @sgtstamp VARCHAR(25)
DECLARE @SORTIMENTO INT
DECLARE @rownumb INT
DECLARE @TAM VARCHAR(25)
--New variable to know if @ref changed
DECLARE @refactual VARCHAR(18)

-- Cursor Variables (END) --


OPEN cur1

FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumb

SET @refactual = ''

WHILE @@FETCH_STATUS = 0

BEGIN

IF @refactual <> @ref
BEGIN
INSERT INTO SGT (sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis,
usrdata, usrhora, usrinis)
VALUES
(RTRIM(LEFT(NEWID(),23)),RTRIM(@ref), 'Z', 99,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),
'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM')
SET @refactual = @ref
END


INSERT INTO SGT (sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis,
usrdata, usrhora, usrinis)
VALUES
(RTRIM(@sgtstamp),RTRIM(@ref), RTRIM(@Tamanho),@rownumb,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),
'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM')

FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumb
END

CLOSE cur1
DEALLOCATE cur1

This Cursor is Ok and make what i pretending

Best regards

Luis Santos



Post #1371008
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse