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 5, 2008 8:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:18 AM
Points: 1,533, Visits: 2,259
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
Post #512190
Posted Thursday, June 5, 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 @ 1:42 AM
Points: 20,799, Visits: 32,717
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 5, 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: Thursday, November 20, 2014 9:51 AM
Points: 534, Visits: 1,042
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 5, 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: Thursday, November 20, 2014 9:51 AM
Points: 534, Visits: 1,042
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 5, 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 @ 1:42 AM
Points: 20,799, Visits: 32,717
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 5, 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: Thursday, November 20, 2014 9:51 AM
Points: 534, Visits: 1,042
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 5, 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 @ 1:42 AM
Points: 20,799, Visits: 32,717
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 5, 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: Thursday, November 20, 2014 9:51 AM
Points: 534, Visits: 1,042
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 5, 2008 9:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:18 AM
Points: 1,533, Visits: 2,259
Taking out the parentesis arround the select statement fixed it.

The Redneck DBA
Post #512255
Posted Tuesday, July 9, 2013 12:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 3, Visits: 29
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