SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using IF condition on Cursor


Using IF condition on Cursor

Author
Message
luissantos
luissantos
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 749
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 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
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
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



Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5094 Visits: 5478
First of all, your current code in your cursor has some bugs:
-you never set @TAM variable,
-if no records found in SGT table, @TAM 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
luissantos
luissantos
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 749
Hello eugene

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

Many thanks
Luis Santos



Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5094 Visits: 5478
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
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 3317
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


luissantos
luissantos
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 749
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



ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2074 Visits: 10383
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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
luissantos
luissantos
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 749
Hello Chris

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

Best regards,
Luis Santos



Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5094 Visits: 5478

...
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
carlosaamaral
carlosaamaral
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 1049
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=SUA_COLUNA FROM ...... ter a certeza que retornará somente uma linha...

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

Um abraço,

Carlos


Suggestion by Chris perfect - No Cursor...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search