SQL Server 預存程序實戰三例：驗證、輸出參數、高效分頁

例 1：簡單登入驗證

CREATE PROC proc_stu @sname VARCHAR(20), @pwd VARCHAR(20)
AS
SELECT * FROM ren WHERE sname = @sname AND pwd = @pwd;
GO
-- 執行
EXEC proc_stu 'admin', 'admin';

例 2：帶輸出參數的驗證

CREATE PROCEDURE VALIDATE
    @USERNAME CHAR(20),
    @PASSWORD CHAR(20),
    @LEGAL BIT OUTPUT
AS
IF EXISTS (SELECT * FROM REN WHERE SNAME = @USERNAME AND PWD = @PASSWORD)
    SELECT @LEGAL = 1;
ELSE
    SELECT @LEGAL = 0;
GO
-- 呼叫範例
DECLARE @isLegal BIT;
EXEC VALIDATE 'admin', 'admin', @isLegal OUTPUT;
SELECT @isLegal;  -- 1 代表合法

例 3：百萬級資料高效分頁（鍵值游標法）

CREATE PROCEDURE pageTest
    @FirstID NVARCHAR(20) = NULL,   -- 當頁第一筆排序鍵值
    @LastID  NVARCHAR(20) = NULL,   -- 當頁最後一筆排序鍵值
    @isNext  BIT = NULL,            -- 1=下一頁, 0=上一頁
    @allCount INT OUTPUT,           -- 總筆數
    @pageSize INT OUTPUT,           -- 頁大小
    @CurPage INT                    -- 0=首頁, -1=末頁, >0=指定頁
AS
SET NOCOUNT ON;

IF @CurPage = 0  -- 首頁
BEGIN
    SELECT @allCount = COUNT(*) FROM Product_test;
    SET @pageSize = 10;
    SELECT TOP (@pageSize) ProductId, ProductName, Introduction
    FROM Product_test
    ORDER BY ProductId;
END
ELSE IF @CurPage = -1  -- 末頁
BEGIN
    SELECT TOP (@pageSize) ProductId, ProductName, Introduction
    FROM (
        SELECT TOP (@pageSize) ProductId, ProductName, Introduction
        FROM Product_test
        ORDER BY ProductId DESC
    ) t
    ORDER BY ProductId;
END
ELSE  -- 中間頁
BEGIN
    IF @isNext = 1  -- 下一頁
        SELECT TOP (@pageSize) ProductId, ProductName, Introduction
        FROM Product_test
        WHERE ProductId > @LastID
        ORDER BY ProductId;
    ELSE  -- 上一頁
        SELECT TOP (@pageSize) ProductId, ProductName, Introduction
        FROM (
            SELECT TOP (@pageSize) ProductId, ProductName, Introduction
            FROM Product_test
            WHERE ProductId < @FirstID
            ORDER BY ProductId DESC
        ) t
        ORDER BY ProductId;
END
GO

核心優勢

• 避免 OFFSET/FETCH 或 NOT IN 掃描大量前置資料列。
• 利用叢集索引鍵值直接定位，效能隨資料量線性增長，百萬級毫秒級回應。
• 輸出參數回傳總筆數與頁大小，前端直接計算總頁數。

呼叫示例

-- 首頁
EXEC pageTest @CurPage = 0;

-- 下一頁（假設首頁最後一筆 ProductId = 10）
EXEC pageTest @FirstID = 1, @LastID = 10, @isNext = 1, @CurPage = 2;