儲存程序是一組為完成特定功能而編寫、經編譯後儲存在資料庫中的 SQL 陳述式集合。使用者透過呼叫程序名稱並傳遞參數即可執行。它類似程式語言中的方法,支援邏輯控制、資料操作、輸入/輸出參數、結果集與回傳值。
儲存在 master 資料庫,名稱以 sp_ 開頭,可跨資料庫呼叫。常用範例:
EXEC sp_databases; -- 檢視資料庫
EXEC sp_tables; -- 檢視資料表
EXEC sp_columns student; -- 檢視欄位
EXEC sp_helpindex student;-- 檢視索引
EXEC sp_rename 'student', 'stuInfo'; -- 重命名資料表
EXEC sp_helptext 'sp_stored_procedures'; -- 檢視程序定義
CREATE PROC | PROCEDURE proc_name
[{@param data_type} [= default] [OUTPUT], ...]
AS
SQL_statements
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'proc_get_student')
DROP PROC proc_get_student;
GO
CREATE PROC proc_get_student
AS
SELECT * FROM student;
GO
EXEC proc_get_student;
ALTER PROC proc_get_student
AS
SELECT * FROM student;
CREATE PROC proc_find_stu(@startId INT, @endId INT)
AS
SELECT * FROM student WHERE id BETWEEN @startId AND @endId;
GO
EXEC proc_find_stu 2, 4;
CREATE PROC proc_findStudentByName
@name VARCHAR(20) = '%j%',
@nextName VARCHAR(20) = '%'
AS
SELECT * FROM student WHERE name LIKE @name AND name LIKE @nextName;
GO
EXEC proc_findStudentByName; -- 使用預設值
EXEC proc_findStudentByName '%o%', 't%'; -- 自訂參數
CREATE PROC proc_getStudentRecord
@id INT,
@name VARCHAR(20) OUT,
@age VARCHAR(20) OUTPUT
AS
SELECT @name = name, @age = age FROM student WHERE id = @id;
GO
-- 呼叫範例
DECLARE @id INT = 7, @name VARCHAR(20), @temp VARCHAR(20) = '1';
EXEC proc_getStudentRecord @id, @name OUT, @temp OUTPUT;
SELECT @name, @temp;
CREATE PROC proc_temp WITH RECOMPILE
AS SELECT * FROM student;
CREATE PROC proc_temp_encryption WITH ENCRYPTION
AS SELECT * FROM student;
CREATE PROC proc_cursor
@cur CURSOR VARYING OUTPUT
AS
SET @cur = CURSOR FORWARD_ONLY STATIC FOR
SELECT id, name, age FROM student;
OPEN @cur;
GO
-- 呼叫端
DECLARE @exec_cur CURSOR, @id INT, @name VARCHAR(20), @age INT;
EXEC proc_cursor @cur = @exec_cur OUTPUT;
FETCH NEXT FROM @exec_cur INTO @id, @name, @age;
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'id: ' + CAST(@id AS VARCHAR) + ', name: ' + @name + ', age: ' + CAST(@age AS CHAR);
FETCH NEXT FROM @exec_cur INTO @id, @name, @age;
END
CLOSE @exec_cur; DEALLOCATE @exec_cur;
-- 方式一:ROW_NUMBER + 範圍
CREATE PROC pro_page
@startIndex INT, @endIndex INT
AS
SELECT COUNT(*) FROM product;
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY pid) AS rowId, *
FROM product
) temp WHERE temp.rowId BETWEEN @startIndex AND @endIndex;
-- 方式二:頁碼/頁大小
CREATE PROC pro_stu
@pageIndex INT, @pageSize INT
AS
DECLARE @startRow INT, @endRow INT;
SET @startRow = (@pageIndex - 1) * @pageSize + 1;
SET @endRow = @startRow + @pageSize - 1;
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY id ASC) AS number FROM student
) t WHERE t.number BETWEEN @startRow AND @endRow;
RAISERROR(
{msg_id | msg_str | @local_variable},
severity, state,
[argument [, ...n]]
[WITH option [, ...n]]
);
msg_str:自訂訊息,最長 2047 字元severity:0-18 為使用者可指定,19+ 需系統管理員權限state:0-255,協助追蹤錯誤來源儲存程序是 SQL Server 開發的核心利器,善用參數化、加密、分頁與錯誤處理,能大幅提升效能、安全性與維護性。建議在專案中建立統一的程式碼範本,規範命名與錯誤處理模式。