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 Tuesday, October 9, 2012 4:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:06 AM
Points: 271, Visits: 580
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






Post #1370624
Posted Tuesday, October 9, 2012 5:12 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1370626
Posted Tuesday, October 9, 2012 5:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:06 AM
Points: 271, Visits: 580
Hello eugene

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

Many thanks
Luis Santos



Post #1370627
Posted Tuesday, October 9, 2012 5:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
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 ).


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

How to post your question to get the best and quick help
Post #1370629
Posted Tuesday, October 9, 2012 8:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:45 PM
Points: 1,080, Visits: 3,170
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 (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

Post #1370653
Posted Wednesday, October 10, 2012 2:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:06 AM
Points: 271, Visits: 580
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



Post #1370758
Posted Wednesday, October 10, 2012 2:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 1,037, Visits: 6,950
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
Post #1370771
Posted Wednesday, October 10, 2012 3:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:06 AM
Points: 271, Visits: 580
Hello Chris

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

Best regards,
Luis Santos



Post #1370789
Posted Wednesday, October 10, 2012 3:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188

...
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1370799
Posted Wednesday, October 10, 2012 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 10:12 AM
Points: 29, Visits: 997
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...

Post #1370889
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse