Using IF condition on Cursor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Using IF condition on Cursor

11 posts, Page 2 of 2

««1 2

Using IF condition on Cursor

Rate Topic

Display Mode

Topic Options

Author

Message

luissantos

Posted Wednesday, October 10, 2012 9:28 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:34 AM
Points: 244, Visits: 480

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 »

11 posts, Page 2 of 2

««1 2

Permissions