ORDER BY in a cursor
-
Is there a limitation on being able to order the select statement you are using for a cursor?
In the query below, if I comment out the order by clause, it works, but if I leave it there it won't run.
DECLARE @DB varchar(25)
DECLARE DBCursor CURSOR FOR
(
SELECT [Name]
FROM master.dbo.sysdatabases
WHERE
[NAME] LIKE '%PLUS' OR [NAME] LIKE 'BILLING%'
ORDER BY [DBID] DESC
)
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DB
WHILE @@fetch_status = 0
BEGIN -- WHILE BEGIN
PRINT @DB
FETCH NEXT FROM DBCursor INTO @DB
END -- WHILE END
CLOSE DBCursor
DEALLOCATE DBCursor
The Redneck DBA
-
Try this:
DECLARE @DB varchar(25)
DECLARE DBCursor CURSOR FOR
SELECT [name]
FROM master.sys.databases
WHERE
[name] LIKE '%PLUS' OR [NAME] LIKE 'BILLING%'
ORDER BY [database_id] DESC
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DB
WHILE @@fetch_status = 0
BEGIN -- WHILE BEGIN
PRINT @DB
FETCH NEXT FROM DBCursor INTO @DB
END -- WHILE END
CLOSE DBCursor
DEALLOCATE DBCursor
😎
-
Hi,
I believe cursor dont like order by. I am not sure. But i tried to run same thing and it didnt work. What you can do is before your cursor start you can enter your required data with order by into some other table. (temp table) and then use select statement from that table.
thanks,
vijay
-
Here you go
CREATE PROCEDURE [dbo].[VD_TEMP_0506] AS
DECLARE @DB varchar(25)
create table #t ([name] varchar(50))
insert into #t
SELECT [Name]
FROM master.dbo.sysdatabases
Where [name] like '%NCR%'
order by [Name]
DECLARE DBCursor CURSOR FOR
(
SELECT [Name]
FROM #t
)
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DB
WHILE @@fetch_status = 0
BEGIN -- WHILE BEGIN
PRINT @DB
FETCH NEXT FROM DBCursor INTO @DB
END -- WHILE END
CLOSE DBCursor
DEALLOCATE DBCursor
GO
-
ORDER BY in a cursor works. Copy my code and test it.
😎
-
Hi,
Yes Lynn. you are right. It works. I said i m not sure because i never come up with the situation.
It is just bracket. ()
DECLARE @DB varchar(25)
DECLARE DBCursor CURSOR FOR
SELECT [Name]
FROM master.dbo.sysdatabases
WHERE
[NAME] LIKE '%PLUS' OR [NAME] LIKE 'BILLING%'
ORDER BY [DBID] DESC
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DB
WHILE @@fetch_status = 0
BEGIN -- WHILE BEGIN
PRINT @DB
FETCH NEXT FROM DBCursor INTO @DB
END -- WHILE END
CLOSE DBCursor
DEALLOCATE DBCursor
And it will work.
-
Curious, looking at your code, are you running SQL 2005 or SQL 2000?
😎
-
I have both opened. We have some server on 2000 and some on 2005. so can use 2005 and enterprise manager as well.
When i take jason's code i need to use 2000 and ur code - 2005.
-
Taking out the parentesis arround the select statement fixed it.
The Redneck DBA
-
Thanks Lynn - good solutions can be so simple - and so unintuitive...
P.
-
Please note: 5 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Just because it is old doesn't mean some of us won't find it to be useful information.
The Redneck DBA
-
Never said it would. Hopefully however it will prevent people wasting time trying to solve a problem that's been solved for 5 years (as I did this morning before I noticed the date)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Guys
It saved me lots of time after a wasted afternoon. So, it's an oldie but goldie!
P.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply