order by clause in cursor
How can I order data in a cursor? Can we use the order by clause
?
Because I need to sort the da开发者_如何学Gota first.
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
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
I know there was some problem with that but it can be solved using
select * from (select a, b from t order by a)
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?
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
精彩评论