利用動態執行技術,我們能更輕鬆地編寫 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 所示。
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 不支援,建議避免使用。
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)