order by clause in cursor

Question

How can I order data in a cursor? Can we use the order by clause?

Because I need to sort the data first.

Answer 1

Like that:

DECLARE cur CURSOR FOR
(
    SELECT * FROM 
    (
         SELECT TOP 9999999999 -- Necessary...
              col_1 
             ,... 
             ,col_n 
         FROM TABLE_XY 
         ORDER BY WHATEVER
    ) AS TempTableBecauseSqlServerSucks 
)

Real-world example:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DELDATA_Delete_NON_SOFT_ByForeignKeyDependency]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DELDATA_Delete_NON_SOFT_ByForeignKeyDependency]
GO





-- =============================================
-- Author:      Stefan Steiger
-- Create date: 22.06.2012
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_DELDATA_Delete_NON_SOFT_ByForeignKeyDependency]
AS 
BEGIN 
    DECLARE @ThisCmd varchar(500)


    DECLARE cur CURSOR
    FOR 
    (   
        SELECT * FROM 
        (           
            SELECT TOP 9999999999
                -- Lvl
                --,TableName
                --,
                'DELETE FROM [' + TableName + '] WHERE [' + 
                (
                    SELECT 
                        INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
                    FROM INFORMATION_SCHEMA.COLUMNS  
                    WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = V_DELDATA_Tables_All.TableName 
                    AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%Status'
                ) + '] = 99; ' AS cmd

            FROM V_DELDATA_Tables_All
            WHERE (1=1) 
            AND TableName LIKE  'T_%' 
            AND TableName NOT LIKE  'T_Ref_%' 
            AND TableName NOT LIKE  'T_RPT_%' 
            AND TableName NOT LIKE  'T_Import_%' 
            AND TableName NOT LIKE  'T_Export_%' 

            ORDER BY Lvl DESC, TableName ASC 
        ) AS SqlServerSucks

    ) --End For

    OPEN cur 

    FETCH NEXT FROM cur INTO @ThisCmd 
    WHILE @@fetch_status = 0 
    BEGIN 
        PRINT @ThisCmd 
        --EXECUTE(@ThisCmd) 
        FETCH NEXT FROM cur INTO @ThisCmd 
    END 
    CLOSE cur 
    DEALLOCATE cur 

END


GO

Answer 2

I am not sure what database you are using but it is possible in SQL Server.

for e.g.:

DECLARE vendor_cursor CURSOR FOR 
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

Answer 3

I know there was some problem with that but it can be solved using

select * from (select a, b from t order by a)

Answer 4

For some reason the other sub-query based answers did not work for me. It kept dropping rows, SQL Server 2012. This particular case was selecting ~100 rows from a view of some static data crossed with table data.

The cure was to declare the cursor as "forward_only static":

    declare mappingsCursor cursor local forward_only static for
        select top 2000000000
               a, b, c, d
          from MappingsView
         order by a, b, c, d;

Link: Why an cursor opened for a select with ORDER by does not reflect updates to the subsequent table

Anyone know the default cursor type when there is an order by? Why wouldn't it always work for "static" data?

Answer 5

I had similar problem. I used table return function for CURSOR. in function's select statement, ORDER BY did not worked. BUT in CURSOR FOR, it worked.

DECLARE {cursor_name} CURSOR FOR 
  {select_statement | table_return_function} 
  ORDER BY {column_name} ASC|DESC