萬盛學電腦網

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

動態執行技術簡化動態排序存儲過程

四、動態執行

利用動態執行技術,我們能更輕鬆地編寫 GetSortedShippers 存儲過程。只需動態構造 SELECT 語句,再用 EXEC() 執行。若傳遞參數為欄位名稱,存儲過程可大幅精簡:

ALTER PROC GetSortedShippers
    @ColName AS sysname
AS
    EXEC('SELECT * FROM Shippers ORDER BY ' + @ColName)

在 SQL Server 2000 和 7.0 中,可用系統存儲過程 sp_ExecuteSQL 替代 EXEC()。根據線上叢書 (BOL),sp_ExecuteSQL 優於 EXEC()。一般情況下,若滿足三個條件(僅使用 DML、所有引用對象擁有者相同、未使用動態命令),可在不授予底層對象權限的情況下授予執行存儲過程權限。上述過程不滿足第三條件,因此必須顯式授予使用者對 Shippers 表的 SELECT 權限。

同理,也可修改存儲過程接受欄位編號參數,如 Listing 8 所示。

Listing 8:以欄位編號為參數的動態執行 (較長版本)

ALTER PROC GetSortedShippers
    @ColNumber AS int
AS
    DECLARE @cmd AS varchar(8000)
    SET @cmd = 'SELECT * FROM Shippers ORDER BY ' +
        CASE @ColNumber
            WHEN 1 THEN 'ShipperID'
            WHEN 2 THEN 'CompanyName'
            WHEN 3 THEN 'Phone'
            ELSE 'NULL'
        END
    EXEC(@cmd)

注意:使用函數時,應在變量中構造 SELECT 語句而非直接在 EXEC() 內,讓 CASE 表達式動態決定排序欄位。另有一種更簡短的寫法:T-SQL 允許在 ORDER BY 中指定 SELECT 清單中欄位的位置 (Listing 9)。此寫法符合 SQL-92 標準,但 ANSI SQL-99 不支援,建議避免使用。

Listing 9:以欄位編號為參數的動態執行 (較短版本)

ALTER PROC GetSortedShippers
    @ColNumber AS int
AS
    DECLARE @cmd AS varchar(8000)
    SET @cmd = 'SELECT * FROM Shippers ORDER BY ' + CAST(@ColNumber AS varchar(4))
    EXEC(@cmd)
copyright © 萬盛學電腦網 all rights reserved