Using IF condition on Cursor

  • Hello

    I have build this cursor that work correctly, but i need to changing then to out an IF condition if @tamanho exist.

    my cursor code modified with IF Condition is below:

    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 = '30110'--IN('29913','30110')

    GROUP BY referencia, TAMANHO

    -- Cursor Variables --

    DECLARE @ref VARCHAR(18)

    DECLARE @TAMANHO VARCHAR(25)

    DECLARE @sgtstamp VARCHAR(25)

    DECLARE @SORTIMENTO INT

    DECLARE @rownumb INT

    DECLARE @tam-2 int

    -- Cursor Variables (END) --

    OPEN cur1

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

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Here i need an IF condition to test

    SELECT tam FROM sgt WHERE tam = 'Y' AND ref = @ref -- I need to check if already exist Y on field TAM of

    -- my table SGT

    -- I need to make 2 INSERT Statements

    IF @tamanho <> @tam-2

    BEGIN

    INSERT INTO SGT(sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis,

    usrdata, usrhora, usrinis)

    VALUES

    (RTRIM(@sgtstamp),RTRIM(@ref), 'Y',@rownumb,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),

    'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM')

    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')

    -- If exist, i make only one INSERT STATEMENT

    IF @tamanho = @tam-2

    BEGIN

    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')

    END

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

    END

    END

    CLOSE cur1

    DEALLOCATE cur1

    Is my Cursor Code build this way is Correct ??

    Many thanks

    Luis Santos

  • First of all, your current code in your cursor has some bugs:

    -you never set @tam-2 variable,

    -if no records found in SGT table, @tam-2 will be NULL and your IF with "<>" is not going to work

    -END for the BEGIN for the first IF is out of place. It should not be after FETCH NEXT, but before the second IF

    The second, and most important: you don't need a cursor at all to do what you're doing.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hello eugene

    could you give me the sample on how to build the code ?

    Many thanks

    Luis Santos

  • Sorry mate, it's 20 past midnight in my place. You will need to wait for someone in US to pick up this thread.

    I can give you some ideas how to proceed.

    1. Please specify more requirements details

    2. If you follow the link at the bottom of my signature, you will find tips about how to make your post helpful to helpers )

    3. You need to clarify the rule you apply for checking what exists and what doesn't.

    What about if two rows already exits in STG? etc.

    Good Night (to me :-D).

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi Luis

    As Eugene said, this could be replaced without using a cursor with something like the following.

    This example may not be the best way to do it, but it has the elements from you procedure sort of replicated to make it a bit easier to follow.

    Also as I have nothing to test this against I can't guarantee that it will even work :ermm: (may have some typos or logic errors)

    ;with

    -- Using the query for the cursor

    cursorReplacement AS (

    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 = '30110'--IN('29913','30110')

    GROUP BY referencia, TAMANHO

    ),

    -- select the tam for each row in the cursor replacement. Only used for the NOT EQUAL query

    tam as (

    select isnull(tam,'#') tam

    from sgt s

    left outer join cursorReplacement c ON s.ref = c.referencia

    where tam = 'Y'

    ),

    -- return a row for insert from cursor replacement with tam set to 'Y'

    tamanhoNEtam AS (

    select sgtstamp, referencia as ref, 'Y' as tam,

    rownumb as pos, sortimento as dimcol,

    CONVERT(date,GETDATE()) as ousrdata,CONVERT(time, GETDATE()) as ousrhora,'ADM' as ousrinis,

    CONVERT(date,GETDATE()) as usrdata,CONVERT(time,GETDATE()) as usrhora,'ADM' as usrinis

    from cursorReplacement c

    where not exists ( select 1 from tam t WHERE t.tam = c.tamanho )

    ),

    -- return a row for the insert from the cursor replacement (covers the second insert for the IF NOT EQUAL as well as the IF EQUAL insert)

    tamanhotam AS (

    select sgtstamp, referencia as ref, tamanho as tam,

    rownumb as pos, sortimento as dimcol,

    CONVERT(date,GETDATE()) as ousrdata,CONVERT(time, GETDATE()) as ousrhora,'ADM' as ousrinis,

    CONVERT(date,GETDATE()) as usrdata,CONVERT(time,GETDATE()) as usrhora,'ADM' as usrinis

    from cursorReplacement c

    )

    -- INSERT INTO SGT(sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis)

    SELECT *

    FROM tamanhoNEtam

    UNION ALL

    SELECT *

    FROM tamanhotam

  • Hello Micky

    The purpose for using a Cursor is because i need to run an Update not for one "referencia" = Article, but for more and less 170000, and for each one the number of "tam" could be different like 34,35,36,37,38,39 or for another one ; 32,33,34,35 and so on....

    Note : for testing my cursor the WHERE condition have only one referencia.

    I think it is possible to alter my cursor to make an IF condition, ok i not doing this right, but if you can explain how to do this i will be grateful.

    I hope you can understand what i pretend.

    Many thanks for your reply,

    Luis Santos

  • luissantos (10/10/2012)


    Hello Micky

    The purpose for using a Cursor is because i need to run an Update not for one "referencia" = Article, but for more and less 170000, and for each one the number of "tam" could be different like 34,35,36,37,38,39 or for another one ; 32,33,34,35 and so on....

    Note : for testing my cursor the WHERE condition have only one referencia.

    I think it is possible to alter my cursor to make an IF condition, ok i not doing this right, but if you can explain how to do this i will be grateful.

    I hope you can understand what i pretend.

    Many thanks for your reply,

    Luis Santos

    Looks to me like Eugene and Micky are correct - there's no need to cripple this process by using a cursor.

    Have a look through the following code, I think the logic should be very clear.

    If it doesn't meet your requirement then I think the spec needs a few more details.

    SELECT

    referencia= RTRIM(referencia),

    TAMANHO= RTRIM(TAMANHO),

    sgtstamp= RTRIM(LEFT(NEWID(),23)),

    SORTIMENTO= min(QTD),

    Rownumb= ROW_NUMBER() OVER(PARTITION BY referencia ORDER BY referencia,tamanho ASC)

    INTO #arttamcor

    FROM arttamcor

    WHERE referencia = '30110' --IN('29913','30110')

    GROUP BY referencia, TAMANHO

    -- insert tam = 'Y' rows into SGT where they don't already exist

    INSERT INTO SGT (

    sgtstamp,

    ref,

    Tam,

    pos,

    dimcol,

    ousrdata,

    ousrhora,

    ousrinis,

    usrdata,

    usrhora,

    usrinis)

    SELECT

    sgtstamp,

    referencia,

    'Y',

    rownumb,

    SORTIMENTO,

    CAST(GETDATE() AS DATE),

    CONVERT (time, GETDATE()),

    'ADM',

    CAST(GETDATE() AS DATE),

    CONVERT (time, GETDATE()),

    'ADM'

    FROM #arttamcor a

    WHERE NOT EXISTS (SELECT 1 FROM SGT s WHERE s.ref = a.ref AND s.tam = 'Y')

    -- insert all of the rows

    INSERT INTO SGT(

    sgtstamp,

    ref,

    Tam,

    pos,

    dimcol,

    ousrdata,

    ousrhora,

    ousrinis,

    usrdata,

    usrhora,

    usrinis)

    SELECT

    sgtstamp,

    referencia,

    Tamanho,

    rownumb,

    SORTIMENTO,

    CAST(GETDATE() AS DATE),

    CONVERT (time, GETDATE()),

    'ADM',

    CAST(GETDATE() AS DATE),

    CONVERT (time, GETDATE()),

    'ADM'

    FROM #arttamcor a


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Hello Chris

    Thanks for your reply, for me is more readable to understand why the others suggestions works.

    Best regards,

    Luis Santos

  • ...

    The purpose for using a Cursor is because i need to run an Update not for one "referencia" = Article, but for more and less 170000...

    That your line is the reason for "not using Cursor"!

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • sorry.... Portuguese...

    Somente um parenteses Luis...

    --Here i need an IF condition to test

    SELECT tam FROM sgt WHERE tam = 'Y' AND ref = @ref -- I need to check if already exist Y on field TAM of

    Para colocar em uma variável

    SELECT @tam-2=SUA_COLUNA FROM ...... ter a certeza que retornará somente uma linha...

    aí depois vc pode utilizar if @tam-2=.....

    Um abraço,

    Carlos

    Suggestion by Chris perfect - No Cursor...

  • 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-2 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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply