資料庫查詢結果動態排序（四）：動態執行 EXEC/sp_executesql、權限模型與欄位編號簡短寫法

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

ALTER PROC GetSortedShippers @ColNumber AS INT
AS
DECLARE @cmd 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)

ALTER PROC GetSortedShippers @ColNumber AS INT
AS
DECLARE @cmd VARCHAR(8000)
SET @cmd = 'SELECT * FROM Shippers ORDER BY ' + CAST(@ColNumber AS VARCHAR(4))
EXEC(@cmd)