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