萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2000 使用者定義函數實現動態排序

五、使用者定義函數 (UDF) 實現動態排序

若您使用的是 SQL Server 2000,想要編寫一個接受欄位名稱或編號作為參數並返回排序結果集的使用者定義函數 (UDF),大多數開發者的第一直覺可能如 Listing 10 所示。

Listing 10:以欄位名稱作為參數的 UDF 初嘗

CREATE FUNCTION ufn_GetSortedShippers
(
    @ColName AS sysname
)
RETURNS TABLE
AS
RETURN
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
    WHEN 'ShipperID' THEN CASE SIGN(ShipperID)
        WHEN -1 THEN '-'
        WHEN 0 THEN '+'
        WHEN 1 THEN '+'
        ELSE NULL
    END +
    RIGHT(REPLICATE('0', 10) + CAST(ABS(ShipperID) AS varchar(10)), 10)
    WHEN 'CompanyName' THEN CompanyName
    WHEN 'Phone' THEN Phone
    ELSE NULL
END

然而,SQL Server 會拒絕這個函數並拋出錯誤:

Server: Msg 1033, Level 15, State 1, Procedure ufn_GetSortedShippers, Line 24
The ORDER BY clause is invalid in views, inline functions, and subqueries, unless TOP is also specified.

錯誤訊息中的關鍵字是「unless」。SQL Server 2000 不允許在檢視表、內嵌函數或子查詢中出現 ORDER BY 子句,因為它們應返回一個無序的表。但若加上 TOP 關鍵字,ORDER BY 便可用於決定返回的行。因此,我們可以用一個技巧:將 SELECT * 改為 SELECT TOP 100 PERCENT *,即可成功建立接受欄位名稱或編號並返回排序結果的函數。

修正後的函數調用範例

SELECT * FROM ufn_GetSortedShippers('ShipperID')

至此,您已掌握數種透過參數決定查詢輸出記錄順序的方法。在開發允許使用者指定排序欄位的應用程式時,可靈活運用本文介紹的 CASE 表達式與動態執行等技巧。

copyright © 萬盛學電腦網 all rights reserved