July 16, 2008 at 10:10 am
I have a cursor that I am fetching a set of rows in alpha_id order and updating a column to show the same order (as the java program reads that column for its ordering). However, my cursor seems to not be fetching the next row in the cursor for some reason and just looping over the same row over and over again (but updating the order variable). Any idea on what I am doing wrong?
DECLARE update_display_order CURSOR fast_forward
FOR select appcode_id from cpi_appcode where category = 'LDCU_CASE_MANAGER' order by code
DECLARE @appcode_id decimal
DECLARE @order decimal
select @order = 0
OPEN update_display_order
FETCH NEXT FROM update_display_order into @appcode_id
WHILE @@fetch_status = 0
BEGIN
select @order = @order+1
update cpi_appcode set display_order = @order where appcode_id = @appcode_id
PRINT @order
PRINT @appcode_id
FETCH NEXT FROM update_display_order into @appcode_id
END
CLOSE update_display_order
DEALLOCATE update_display_order
GO
Sample output looks like this from the print statements:
(1 row(s) affected)
1
1033
(1 row(s) affected)
2
1033
(1 row(s) affected)
3
1033
(1 row(s) affected)
4
1033
Note the appcode_id in the result set used to form the cursor only has 1033 as the first row and the next is 1034 but the fetch next isn't updating the @appcode_id for some reason.
July 16, 2008 at 10:30 am
I don't have an answer to your cursor question, but maybe a solution that eliminates the cursor would be acceptable.
First a check, are you using SQL 2005? (I assume so, based on the forum this is posted in.)
If you are, try something like this
DECLARE @cpi_appcode TABLE (
AppCode_id INT,
Display_Order int
)
INSERT INTO @cpi_appcode (AppCode_id) VALUES ( 1031 )
INSERT INTO @cpi_appcode (AppCode_id) VALUES ( 1035 )
INSERT INTO @cpi_appcode (AppCode_id) VALUES ( 1032 )
INSERT INTO @cpi_appcode (AppCode_id) VALUES ( 1037 )
INSERT INTO @cpi_appcode (AppCode_id) VALUES ( 1039 )
INSERT INTO @cpi_appcode (AppCode_id) VALUES ( 1034 )
INSERT INTO @cpi_appcode (AppCode_id) VALUES ( 1033 )
SELECT * FROM @cpi_appcode
UPDATE @cpi_appcode SET Display_order = generatedOrder
FROM @cpi_appcode ap INNER JOIN
(SELECT appcode_id, ROW_NUMBER() OVER (ORDER BY AppCode_ID) AS generatedOrder
FROM @cpi_appcode) Ordered
ON ap.AppCode_id = Ordered.appcode_id
SELECT * FROM @cpi_appcode ORDER BY display_order
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 10:48 am
This particular DB is actually still in 2000, although most are in 2005 so apologies for posting to wrong forum. But using your same logic I can generate the row_number type value by doing:
select code, (
select count(*)
from cpi_appcode a2
where category = 'LDCU_CASE_MANAGER'
and a1.code >= a2.code)
from cpi_appcode a1
where category = 'LDCU_CASE_MANAGER'
and wrapping that in an update. So thanks for the nudge.
July 16, 2008 at 10:56 am
You can, but beware if you have more than a couple hundred rows. That kind of correlated subquery/triangular join performs really, really badly on larger row counts.
Maybe a temp table with an identity?
DECLARE @cpi_appcode TABLE (
AppCode_id INT,
Display_Order INT,
category VARCHAR(30)
)
INSERT INTO @cpi_appcode (AppCode_id, category) VALUES ( 1031, 'LDCU_CASE_MANAGER' )
INSERT INTO @cpi_appcode (AppCode_id, category) VALUES ( 1035, 'LDCU_CASE_MANAGER' )
INSERT INTO @cpi_appcode (AppCode_id, category) VALUES ( 1032, 'LDCU_CASE_MANAGER' )
INSERT INTO @cpi_appcode (AppCode_id, category) VALUES ( 1037, 'LDCU_CASE_MANAGER' )
INSERT INTO @cpi_appcode (AppCode_id, category) VALUES ( 1039, 'LDCU_CASE_MANAGER' )
INSERT INTO @cpi_appcode (AppCode_id, category) VALUES ( 1034, 'LDCU_CASE_MANAGER' )
INSERT INTO @cpi_appcode (AppCode_id, category) VALUES ( 1033, 'LDCU_CASE_MANAGER' )
CREATE TABLE #TempOrdering (
AppCode_id INT,
Display_Order int IDENTITY
)
INSERT INTO #TempOrdering (AppCode_id)
SELECT AppCode_id FROM @cpi_appcode
where category = 'LDCU_CASE_MANAGER'
ORDER BY AppCode_id
UPDATE @cpi_appcode SET Display_Order = t.Display_Order
FROM @cpi_appcode ca INNER JOIN #TempOrdering t ON ca.AppCode_id = t.AppCode_id
SELECT * FROM @cpi_appcode
DROP TABLE #TempOrdering
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 11:07 am
In this category of that table its only 56 records but there are other categories in the table which would contain much higher sets of data that I might need to do the same thing in so will probably just write it using the temp table approach.
Thanks for the advice.
July 16, 2008 at 11:11 am
Pleasure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply