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»»

ORDER BY in a cursor Expand / Collapse
Author
Message
Posted Thursday, June 05, 2008 8:57 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 1,478, Visits: 2,101
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



Jason Shadonix
MCTS, SQL 2005
Post #512190
Posted Thursday, June 05, 2008 9:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 22,532, Visits: 30,310
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




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #512204
Posted Thursday, June 05, 2008 9:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 08, 2014 5:15 AM
Points: 534, Visits: 1,040
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
Post #512212
Posted Thursday, June 05, 2008 9:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 08, 2014 5:15 AM
Points: 534, Visits: 1,040
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
Post #512216
Posted Thursday, June 05, 2008 9:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 22,532, Visits: 30,310
ORDER BY in a cursor works. Copy my code and test it.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #512218
Posted Thursday, June 05, 2008 9:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 08, 2014 5:15 AM
Points: 534, Visits: 1,040
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.
Post #512231
Posted Thursday, June 05, 2008 9:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 22,532, Visits: 30,310
Curious, looking at your code, are you running SQL 2005 or SQL 2000?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #512233
Posted Thursday, June 05, 2008 9:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 08, 2014 5:15 AM
Points: 534, Visits: 1,040
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.
Post #512249
Posted Thursday, June 05, 2008 9:55 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 1,478, Visits: 2,101
Taking out the parentesis arround the select statement fixed it.

Jason Shadonix
MCTS, SQL 2005
Post #512255
Posted Tuesday, July 09, 2013 12:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 16, 2013 12:14 AM
Points: 3, Visits: 26
Thanks Lynn - good solutions can be so simple - and so unintuitive...

P.
Post #1471450
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse