您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 預存程序實戰詳解：參數、回傳值、巢狀呼叫、分頁與最佳實務

編輯：關於SqlServer

為何需要預存程序？

批次語句無法傳參數、無法模組化重用
檢視表僅支援 SELECT，無法執行增刪改/DDL
將邏輯下沉至資料庫層，減少網路傳輸、提升效能與資料完整性

核心語法

CREATE PROC proc_name
    @param datatype [= default] [OUTPUT], ...
AS
BEGIN
    -- T-SQL 批次
END
GO

四大呼叫/回傳模式

EXEC proc_nameEXEC proc_name @p1=v1, @p2=v2EXEC proc_name @p1=v1, @out OUTPUTEXEC @ret = proc_nameEXEC proc_name

模式	語法	適用場景
無參無回傳	固定邏輯執行
輸入參數	動態篩選/條件
OUTPUT 參數	回傳單一/多值 (非結果集)
RETURN 整數	執行狀態碼 (0=成功)
結果集	回傳查詢結果給用戶端/應用程式

實戰範例

1. 交易控制 + RETURN 狀態碼

CREATE PROC returnProc AS
BEGIN TRAN;
DECLARE @err INT = 0;
INSERT INTO yiren (xingming) VALUES ('王美麗'); SET @err += @@ERROR;
INSERT INTO yiren (yirenid) VALUES (1);        SET @err += @@ERROR;
IF @err > 0 ROLLBACK TRAN ELSE COMMIT TRAN;
RETURN @err;  -- 0 成功，非 0 失敗
GO

DECLARE @ret INT; EXEC @ret = returnProc; SELECT @ret AS 回傳碼;

2. OUTPUT 參數回傳資料

CREATE PROC queryProfile @id INT, @name VARCHAR(50) OUTPUT, @age INT OUTPUT
AS SELECT @name = xingming, @age = nianling FROM yiren WHERE yirenid = @id;
GO

DECLARE @n VARCHAR(50), @a INT;
EXEC queryProfile 1, @n OUTPUT, @a OUTPUT;
PRINT '姓名:' + @n + ' 年齡:' + CAST(@a AS VARCHAR);

3. 巢狀呼叫 (最多 32 層)

CREATE PROC proc_1 @yid INT OUTPUT AS
    SELECT TOP 1 @yid = yirenid FROM yiren WHERE nicheng = '芙蓉姐姐';
    PRINT 'Layer ' + CAST(@@NESTLEVEL AS VARCHAR);
GO

CREATE PROC proc_1_1 @fid INT OUTPUT AS
    DECLARE @yid INT; EXEC proc_1 @yid OUTPUT;
    SELECT @fid = yonghuid FROM fensi WHERE yirenid = @yid ORDER BY yonghuid DESC;
    PRINT 'Layer ' + CAST(@@NESTLEVEL AS VARCHAR);
GO

CREATE PROC proc_1_1_1 AS
    DECLARE @fid INT; EXEC proc_1_1 @fid OUTPUT;
    SELECT * FROM yonghu WHERE yonghuid = @fid;
    PRINT 'Layer ' + CAST(@@NESTLEVEL AS VARCHAR);
GO

EXEC proc_1_1_1;  -- 輸出 Layer 1, Layer 2, Layer 3

系統預存程序速查

sp_databases, sp_help, sp_helpdb, sp_helptext, sp_rename, sp_renamedb, sp_stored_procedures, sp_configure

分頁預存程序 (動態 SQL + sp_executesql)

CREATE PROC queryPage
    @tablename NVARCHAR(50), @idname NVARCHAR(50),
    @pagesize INT, @currentpage INT, @totalpages INT OUTPUT
AS
BEGIN
    DECLARE @sql NVARCHAR(1000), @rowcount INT;
    SET @sql = N'SELECT @rc = COUNT(*) FROM ' + @tablename;
    EXEC sp_executesql @sql, N'@rc INT OUTPUT', @rc = @rowcount OUTPUT;

    SET @totalpages = CEILING(CAST(@rowcount AS FLOAT) / @pagesize);
    IF @currentpage > @totalpages SET @currentpage = @totalpages;
    IF @currentpage < 1 SET @currentpage = 1;

    IF @currentpage = 1
        SET @sql = N'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + N' * FROM ' + @tablename + N' ORDER BY ' + @idname;
    ELSE
        SET @sql = N'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + N' * FROM ' + @tablename +
                   N' WHERE ' + @idname + N' NOT IN (SELECT TOP ' + CAST(@pagesize * (@currentpage - 1) AS VARCHAR) +
                   N' ' + @idname + N' FROM ' + @tablename + N' ORDER BY ' + @idname + N') ORDER BY ' + @idname;
    EXEC (@sql);
END
GO

DECLARE @tp INT; EXEC queryPage 'yiren', 'yirenid', 5, 13, @tp OUTPUT; PRINT '總頁數:' + CAST(@tp AS VARCHAR);