t
runs and never returns - I am assuming I have an infinite look somehow in my
cursor but this is the first SP I've written with a cursor so I'm lost -
Anyone who can point me in the right direction I would be very grateful -
ALSO - is there a way to return to the results screen values at certain
points in a transaction - for example as each row changes can you return a
value to the results screen so you can see that it is doing something at all
?
- No debug authority on the server or I'd do that.
ALTER PROCEDURE dbo.InterrogateRealignRecords AS
/* ----
GO SEE IF THERE ARE ANY CHANGED SHADE
RECORDS FIRST AND RETURN THE COUNT INTO
@.ICOUNTSHDCHGS
---- */
Declare @.numSUCforShade int,@.ICOUNTSHDCHGS int,@.ICOUNTPROPCHGS INT,
@.ICOUNTSUBSEGCHGS INT,@.old_shade_ID Int, @.old_property_ID Int, @.old_subseg_I
D
Int
-- select count(*) from #shades_suc_bu
-- drop table #shades_suc_bu
select Shade_ID, SUC_Code,UPC
into #shades_suc_bu
from shade_Suc
Declare curChangeValues Scroll Cursor For
Select Count(*)
From PPGG_Admin.UPC_Shades_Realigned
Where shade_ID <> old_Shade_ID
Open curChangeValues
Fetch Next From curChangeValues
Into @.iCOUNTSHDCHGS
WHILE (@.@.fetch_status = 0)
BEGIN
if @.ICOUNTSHDCHGS > 0
/* --- */
/* THERE ARE CHANGED SHADE ID'S TO LOOK AT */
/* GO AND GET THE RECORDS SO WE CAN LOOK */
/* AT EACH ONE SEPARATELY */
/* --- */
declare curShadeChngVal scroll cursor for
Select old_Shade_ID, old_property_ID,old_product_Subsegment_I
D
From PPGG_Admin.UPC_Shades_Realigned
Where shade_ID <> old_Shade_ID
Open curShadeChngVal
Fetch Next From curShadeChngVal
Into @.old_shade_ID
,@.old_property_ID
,@.old_subseg_ID
Select @.old_shade_ID
,@.old_property_ID
,@.old_subseg_ID
WHILE (@.@.fetch_status =0)
Begin
/* ---*/
/* DURING THE LOOP CHECK THE SUC TABLE
TO SEE IF ANY UPCS STILL EXIST
FOR THIS SHADE */
/* ---*/
DECLARE curSUCVal scroll cursor for
Select Count(*) from Shade_SUC
where Shade_ID = @.old_Shade_ID
OPEN curSUCVal
fetch next from curSUCVal
into @.numSUCforShade
if @.numSUCforShade > 0
Begin
--declare @.dan int
--set @.dan =1
delete from #shades_suc_bu where Shade_ID = @.old_Shade_ID
end
CLOSE curSUCVal
Deallocate curSUCVal
END
end
Close curChangeValues
Deallocate curChangeValues
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHEY THANKS - I sufficiently feel like an idiot - not everyone is a guru my
friend. But thanks for your tact. (not)
"CBretana" wrote:
> Entire thing is messed up
> 1) Each time you execute a cursor loop you have to Fetch Next INSIDE THE
> LOOP at the end, or you're just processing the same row over and over..
> 2) Same with cursor curShadeChngVal, there's a Ferch Next just befor the
> While Loop, but no Fetch Next at the end of the While loop to move to the
> nect record...
> 3) you have triply nested cursors and the innermost cursor (curSUCVal) see
ms
> not to be looping at all, (THERE'S NO WHILE LOOP FOR IT !!) you're just
> processing the first row of it...
> So why use cursor curSUCVal at all '
> "Dan" wrote:
>|||Entire thing is messed up
1) Each time you execute a cursor loop you have to Fetch Next INSIDE THE
LOOP at the end, or you're just processing the same row over and over..
2) Same with cursor curShadeChngVal, there's a Ferch Next just befor the
While Loop, but no Fetch Next at the end of the While loop to move to the
nect record...
3) you have triply nested cursors and the innermost cursor (curSUCVal) seems
not to be looping at all, (THERE'S NO WHILE LOOP FOR IT !!) you're just
processing the first row of it...
So why use cursor curSUCVal at all '
"Dan" wrote:
> My stored proc is below but I have no idea as to why the proc just spins -
it
> runs and never returns - I am assuming I have an infinite look somehow in
my
> cursor but this is the first SP I've written with a cursor so I'm lost -
> Anyone who can point me in the right direction I would be very grateful -
> ALSO - is there a way to return to the results screen values at certain
> points in a transaction - for example as each row changes can you return a
> value to the results screen so you can see that it is doing something at a
ll?
> - No debug authority on the server or I'd do that.
> ALTER PROCEDURE dbo.InterrogateRealignRecords AS
> /* ----
> GO SEE IF THERE ARE ANY CHANGED SHADE
> RECORDS FIRST AND RETURN THE COUNT INTO
> @.ICOUNTSHDCHGS
> ---- */
> Declare @.numSUCforShade int,@.ICOUNTSHDCHGS int,@.ICOUNTPROPCHGS INT,
> @.ICOUNTSUBSEGCHGS INT,@.old_shade_ID Int, @.old_property_ID Int, @.old_subseg
_ID
> Int
> -- select count(*) from #shades_suc_bu
> -- drop table #shades_suc_bu
> select Shade_ID, SUC_Code,UPC
> into #shades_suc_bu
> from shade_Suc
> Declare curChangeValues Scroll Cursor For
> Select Count(*)
> From PPGG_Admin.UPC_Shades_Realigned
> Where shade_ID <> old_Shade_ID
> Open curChangeValues
> Fetch Next From curChangeValues
> Into @.iCOUNTSHDCHGS
> WHILE (@.@.fetch_status = 0)
> BEGIN
> if @.ICOUNTSHDCHGS > 0
> /* --- */
> /* THERE ARE CHANGED SHADE ID'S TO LOOK AT */
> /* GO AND GET THE RECORDS SO WE CAN LOOK */
> /* AT EACH ONE SEPARATELY */
> /* --- */
> declare curShadeChngVal scroll cursor for
> Select old_Shade_ID, old_property_ID,old_product_Subsegment_I
D
> From PPGG_Admin.UPC_Shades_Realigned
> Where shade_ID <> old_Shade_ID
> Open curShadeChngVal
> Fetch Next From curShadeChngVal
> Into @.old_shade_ID
> ,@.old_property_ID
> ,@.old_subseg_ID
> Select @.old_shade_ID
> ,@.old_property_ID
> ,@.old_subseg_ID
> WHILE (@.@.fetch_status =0)
> Begin
> /* ---*/
> /* DURING THE LOOP CHECK THE SUC TABLE
> TO SEE IF ANY UPCS STILL EXIST
> FOR THIS SHADE */
> /* ---*/
> DECLARE curSUCVal scroll cursor for
> Select Count(*) from Shade_SUC
> where Shade_ID = @.old_Shade_ID
> OPEN curSUCVal
> fetch next from curSUCVal
> into @.numSUCforShade
> if @.numSUCforShade > 0
> Begin
> --declare @.dan int
> --set @.dan =1
> delete from #shades_suc_bu where Shade_ID = @.old_Shade_ID
> end
> CLOSE curSUCVal
> Deallocate curSUCVal
> END
>
> end
> Close curChangeValues
> Deallocate curChangeValues
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Dan, I Just noticed somethiong else...
the First cursor, curChangeValues is defined on
Select Count(*)
From PPGG_Admin.UPC_Shades_Realigned
Where shade_ID <> old_Shade_ID
This doesn't return a set of records at all, it just returns a single count
of records.. .You use cursors (when there's no other alternative) to ITERAT
E
through a COLLECTION of records when you need t oprocess each one
individually... not simply to get data. I suspect you really need to read a
bit about the basics of SQL.
"Dan" wrote:
> My stored proc is below but I have no idea as to why the proc just spins -
it
> runs and never returns - I am assuming I have an infinite look somehow in
my
> cursor but this is the first SP I've written with a cursor so I'm lost -
> Anyone who can point me in the right direction I would be very grateful -
> ALSO - is there a way to return to the results screen values at certain
> points in a transaction - for example as each row changes can you return a
> value to the results screen so you can see that it is doing something at a
ll?
> - No debug authority on the server or I'd do that.
> ALTER PROCEDURE dbo.InterrogateRealignRecords AS
> /* ----
> GO SEE IF THERE ARE ANY CHANGED SHADE
> RECORDS FIRST AND RETURN THE COUNT INTO
> @.ICOUNTSHDCHGS
> ---- */
> Declare @.numSUCforShade int,@.ICOUNTSHDCHGS int,@.ICOUNTPROPCHGS INT,
> @.ICOUNTSUBSEGCHGS INT,@.old_shade_ID Int, @.old_property_ID Int, @.old_subseg
_ID
> Int
> -- select count(*) from #shades_suc_bu
> -- drop table #shades_suc_bu
> select Shade_ID, SUC_Code,UPC
> into #shades_suc_bu
> from shade_Suc
> Declare curChangeValues Scroll Cursor For
> Select Count(*)
> From PPGG_Admin.UPC_Shades_Realigned
> Where shade_ID <> old_Shade_ID
> Open curChangeValues
> Fetch Next From curChangeValues
> Into @.iCOUNTSHDCHGS
> WHILE (@.@.fetch_status = 0)
> BEGIN
> if @.ICOUNTSHDCHGS > 0
> /* --- */
> /* THERE ARE CHANGED SHADE ID'S TO LOOK AT */
> /* GO AND GET THE RECORDS SO WE CAN LOOK */
> /* AT EACH ONE SEPARATELY */
> /* --- */
> declare curShadeChngVal scroll cursor for
> Select old_Shade_ID, old_property_ID,old_product_Subsegment_I
D
> From PPGG_Admin.UPC_Shades_Realigned
> Where shade_ID <> old_Shade_ID
> Open curShadeChngVal
> Fetch Next From curShadeChngVal
> Into @.old_shade_ID
> ,@.old_property_ID
> ,@.old_subseg_ID
> Select @.old_shade_ID
> ,@.old_property_ID
> ,@.old_subseg_ID
> WHILE (@.@.fetch_status =0)
> Begin
> /* ---*/
> /* DURING THE LOOP CHECK THE SUC TABLE
> TO SEE IF ANY UPCS STILL EXIST
> FOR THIS SHADE */
> /* ---*/
> DECLARE curSUCVal scroll cursor for
> Select Count(*) from Shade_SUC
> where Shade_ID = @.old_Shade_ID
> OPEN curSUCVal
> fetch next from curSUCVal
> into @.numSUCforShade
> if @.numSUCforShade > 0
> Begin
> --declare @.dan int
> --set @.dan =1
> delete from #shades_suc_bu where Shade_ID = @.old_Shade_ID
> end
> CLOSE curSUCVal
> Deallocate curSUCVal
> END
>
> end
> Close curChangeValues
> Deallocate curChangeValues
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Seriously, if you are new to cursors then you should stay away from
them for straight data manipulation stuff. Most of the time there are
better solutions without using cursors so until you are VERY confident
that you have enough SQL expertise to know when a cursor is the right
choice it's best to assume that you don't need them at all.
If you need more help with a solution then please come back with DDL
and sample data so that we can understand your requirements. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||OK David,
If I am in need of iterating through a row of records to decide on an action
(update, delete, insert new etc) then what other option do I have in a Store
d
Proc?
Thanks
"David Portas" wrote:
> Seriously, if you are new to cursors then you should stay away from
> them for straight data manipulation stuff. Most of the time there are
> better solutions without using cursors so until you are VERY confident
> that you have enough SQL expertise to know when a cursor is the right
> choice it's best to assume that you don't need them at all.
> If you need more help with a solution then please come back with DDL
> and sample data so that we can understand your requirements. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||Dan,
CAPS not meant to insult, just to emphasize, sorry if tone was rough...
No attempt to insult or denigrate was intended.
And there's a differencve between being intelligence, and education.
EVERYONE (CAPS only to emphasize) is "undeducated" when they begin learning
a
new skill... There's no need to feel offended because you're not yet educate
d
...
From your post, a judgement might be made about your SQL knowledge, but
not about your intelligence. It's what you decide to do next that will
speak to the latter issue !
"Dan" wrote:
> HEY THANKS - I sufficiently feel like an idiot - not everyone is a guru my
> friend. But thanks for your tact. (not)
> "CBretana" wrote:
>|||Maybe a WHERE clause on each of those statements in succession -
probably with EXISTS, but that depends on what you are doing.
UPDATE x
SET ... ?
WHERE EXISTS
(SELECT *
FROM PPGG_Admin.UPC_Shades_Realigne=ADd
WHERE id =3D x.id
AND ... ?)
INSERT INTO z (...)
SELECT ...
FROM PPGG_Admin.UPC_Shades_Realigne=ADd AS T
WHERE NOT EXISTS
(SELECT *
FROM z
WHERE T.id =3D z.id
AND ... ?)
--=20
David Portas
SQL Server MVP
--|||Why do you need to iterate through a collection of records?
It appears from the code you posted the only thing the SP is doing is
deleting selected records from temp table #shades_suc_bu in the line
delete from #shades_suc_bu where Shade_ID = @.old_Shade_ID
Which is sort of pointless because the temp table is not then used for
anything else, and it will dissapear immediately after the SP terminates.
But even if it was doing something, the delete could probably be done in one
SQL statement that properly identified the records that need to be deleted,
instead of using any cursors at all...
But first, what exactly is this SP supposed to be doing ?
"Dan" wrote:
> OK David,
> If I am in need of iterating through a row of records to decide on an acti
on
> (update, delete, insert new etc) then what other option do I have in a Sto
red
> Proc?
> Thanks
> "David Portas" wrote:
>|||Usenet drama...
."Dan" <Dan@.discussions.microsoft.com> wrote in message
news:4F63E75A-9565-464A-AA41-36F7F3B46CDA@.microsoft.com...
> My stored proc is below but I have no idea as to why the proc just spins -
it
> runs and never returns - I am assuming I have an infinite look somehow in
my
> cursor but this is the first SP I've written with a cursor so I'm lost -
> Anyone who can point me in the right direction I would be very grateful -
> ALSO - is there a way to return to the results screen values at certain
> points in a transaction - for example as each row changes can you return a
> value to the results screen so you can see that it is doing something at
all?
> - No debug authority on the server or I'd do that.
> ALTER PROCEDURE dbo.InterrogateRealignRecords AS
> /* ----
> GO SEE IF THERE ARE ANY CHANGED SHADE
> RECORDS FIRST AND RETURN THE COUNT INTO
> @.ICOUNTSHDCHGS
> ---- */
> Declare @.numSUCforShade int,@.ICOUNTSHDCHGS int,@.ICOUNTPROPCHGS INT,
> @.ICOUNTSUBSEGCHGS INT,@.old_shade_ID Int, @.old_property_ID Int,
@.old_subseg_ID
> Int
> -- select count(*) from #shades_suc_bu
> -- drop table #shades_suc_bu
> select Shade_ID, SUC_Code,UPC
> into #shades_suc_bu
> from shade_Suc
> Declare curChangeValues Scroll Cursor For
> Select Count(*)
> From PPGG_Admin.UPC_Shades_Realigned
> Where shade_ID <> old_Shade_ID
> Open curChangeValues
> Fetch Next From curChangeValues
> Into @.iCOUNTSHDCHGS
> WHILE (@.@.fetch_status = 0)
> BEGIN
> if @.ICOUNTSHDCHGS > 0
> /* --- */
> /* THERE ARE CHANGED SHADE ID'S TO LOOK AT */
> /* GO AND GET THE RECORDS SO WE CAN LOOK */
> /* AT EACH ONE SEPARATELY */
> /* --- */
> declare curShadeChngVal scroll cursor for
> Select old_Shade_ID, old_property_ID,old_product_Subsegment_I
D
> From PPGG_Admin.UPC_Shades_Realigned
> Where shade_ID <> old_Shade_ID
> Open curShadeChngVal
> Fetch Next From curShadeChngVal
> Into @.old_shade_ID
> ,@.old_property_ID
> ,@.old_subseg_ID
> Select @.old_shade_ID
> ,@.old_property_ID
> ,@.old_subseg_ID
> WHILE (@.@.fetch_status =0)
> Begin
> /* ---*/
> /* DURING THE LOOP CHECK THE SUC TABLE
> TO SEE IF ANY UPCS STILL EXIST
> FOR THIS SHADE */
> /* ---*/
> DECLARE curSUCVal scroll cursor for
> Select Count(*) from Shade_SUC
> where Shade_ID = @.old_Shade_ID
> OPEN curSUCVal
> fetch next from curSUCVal
> into @.numSUCforShade
> if @.numSUCforShade > 0
> Begin
> --declare @.dan int
> --set @.dan =1
> delete from #shades_suc_bu where Shade_ID = @.old_Shade_ID
> end
> CLOSE curSUCVal
> Deallocate curSUCVal
> END
>
> end
> Close curChangeValues
> Deallocate curChangeValues
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
