Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » ORDER BY in a cursor
Add to briefcase

ORDER BY in a cursor Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jason Shadonix
Posted Thursday, June 05, 2008 8:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:49 AM
Points: 1,409, Visits: 2,032
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
Lynn Pettis
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: Yesterday @ 11:14 PM
Points: 21,832, Visits: 27,862
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
dva2007
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: Tuesday, May 14, 2013 9:05 AM
Points: 530, Visits: 1,020
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
dva2007
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: Tuesday, May 14, 2013 9:05 AM
Points: 530, Visits: 1,020
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
Lynn Pettis
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: Yesterday @ 11:14 PM
Points: 21,832, Visits: 27,862
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
dva2007
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: Tuesday, May 14, 2013 9:05 AM
Points: 530, Visits: 1,020
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
Lynn Pettis
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: Yesterday @ 11:14 PM
Points: 21,832, Visits: 27,862
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
dva2007
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: Tuesday, May 14, 2013 9:05 AM
Points: 530, Visits: 1,020
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
Jason Shadonix
Posted Thursday, June 05, 2008 9:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:49 AM
Points: 1,409, Visits: 2,032
Taking out the parentesis arround the select statement fixed it.

Jason Shadonix
MCTS, SQL 2005
Post #512255
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.