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

SQL Server 常用儲存程序大全：分頁、註冊、系統管理與進階功能

編輯：關於SqlServer

本文整理 SQL Server 開發中高頻使用的儲存程式碼範例，涵蓋分頁查詢、使用者註冊、系統管理、資料庫還原鎖定處理、阿拉伯數字轉中文大寫等實用功能，供開發者參考與復用。

一、分頁查詢儲存程序 (游標法)

適用於 SQL Server 2000/2005 早期版本，利用 sp_cursoropen、sp_cursorfetch 實現伺服器端分頁：

CREATE PROCEDURE [dbo].[GetRecordSet]
    @strSql VARCHAR(8000),    -- 查詢 SQL，如：SELECT * FROM [Users]
    @PageIndex INT,           -- 當前頁碼 (從 1 開始)
    @PageSize INT             -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @p1 INT, @currentPage INT, @RowCount INT, @PageCount INT;
    SET @currentPage = 0; SET @RowCount = 0; SET @PageCount = 0;

    -- 開啟游標取得總筆數
    EXEC sp_cursoropen @p1 OUTPUT, @strSql, @scrollopt=1, @ccopt=1, @rowcount=@RowCount OUTPUT;
    SELECT @PageCount = CEILING(1.0 * @RowCount / @PageSize),
           @currentPage = (@PageIndex - 1) * @PageSize + 1;

    -- 回傳總筆數、總頁數
    SELECT @RowCount AS TotalRows, @PageCount AS TotalPages;

    -- 擷取當頁資料
    EXEC sp_cursorfetch @p1, 16, @currentPage, @PageSize;
    EXEC sp_cursorclose @p1;
    SET NOCOUNT OFF;
END;

現代替代方案：SQL Server 2012+ 建議使用 OFFSET ... FETCH NEXT 語法，效能更佳且語法簡潔。

二、使用者註冊儲存程序

包含帳號/信箱唯一性檢查、自動產生流水號、回傳狀態碼：

CREATE PROCEDURE [dbo].[UserAdd]
    @loginID NVARCHAR(50),   -- 登入帳號
    @password NVARCHAR(50),  -- 密碼 (建議應用層加密後傳入)
    @email NVARCHAR(200)     -- 電子信箱
AS
BEGIN
    DECLARE @userID INT;

    -- 檢查帳號是否存在
    IF EXISTS (SELECT 1 FROM Users WHERE LoginID = @loginID)
        RETURN -1;  -- 帳號已註冊

    -- 檢查信箱是否存在
    IF EXISTS (SELECT 1 FROM Users WHERE Email = @email)
        RETURN -2;  -- 信箱已註冊

    -- 註冊成功：產生新 ID 並寫入
    SELECT @userID = ISNULL(MAX(UserID), 100000) + 1 FROM Users;
    INSERT INTO Users (UserID, LoginID, [Password], UserName, LinkNum, Address, Email, CreateTime, Status)
    VALUES (@userID, @loginID, @password, '', '', '', @email, GETDATE(), 1);
    RETURN @userID;  -- 回傳新使用者 ID
END;

三、常用系統儲存程序速查

1. 擴充屬性管理 (欄位說明文檔)

-- 新增欄位描述
EXEC sp_addextendedproperty 
    'MS_Description', '員工編號', 'user', dbo, 'table', 'Employees', 'column', 'EmpID';

-- 修改欄位描述
EXEC sp_updateextendedproperty 
    'MS_Description', '這是測試欄位', 'user', dbo, 'table', 'Employees', 'column', 'EmpID';

2. 重命名資料庫 / 資料表 / 欄位

-- 重命名資料庫
EXEC sp_renamedb 'OldDBName', 'NewDBName';

-- 重命名資料表
EXEC sp_rename 'OldTableName', 'NewTableName';

-- 重命名欄位
EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN';

3. 檢視儲存程序原始碼

EXEC sp_helptext 'ProcedureName';

4. 安全性與權限管理

-- 建立 SQL 登入
EXEC sp_addlogin @loginame='user1', @passwd='P@ssw0rd', @defdb='MyDB';

-- 授與資料庫存取權
EXEC sp_grantdbaccess @loginame='user1', @name_in_db='user1';

-- 建立資料庫角色並加入成員
EXEC sp_addrole @rolename='AppReaders';
EXEC sp_addrolemember @rolename='AppReaders', @membername='user1';

-- 權限控制 (GRANT/DENY/REVOKE)
GRANT SELECT ON Authors TO Limperator;
DENY SELECT ON Authors TO Limperator;
REVOKE SELECT ON Authors TO Limperator;

四、資料庫還原前強制中斷連線 (Kill SPID)

還原資料庫需獨佔存取，此程序終結指定資料庫的所有作用中連線：

CREATE PROCEDURE dbo.KillSpid (@dbname VARCHAR(20))
AS
BEGIN
    DECLARE @sql NVARCHAR(500), @spid INT;
    SET @sql = N'DECLARE getspid CURSOR FOR 
        SELECT spid FROM sysprocesses WHERE dbid = DB_ID(''' + @dbname + ''')';
    EXEC (@sql);
    OPEN getspid;
    FETCH NEXT FROM getspid INTO @spid;
    WHILE @@FETCH_STATUS <> -1
    BEGIN
        EXEC ('KILL ' + @spid);
        FETCH NEXT FROM getspid INTO @spid;
    END
    CLOSE getspid; DEALLOCATE getspid;
END;

使用方式：EXEC KillSpid 'MyDatabase'; 然後立即執行 RESTORE。

五、阿拉伯數字轉中文大寫函數

支援萬/億單位、零值處理 (用「另」佔位再替換)：

CREATE FUNCTION dbo.Fun_CGNum (@num INT)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @temp INT, @res INT, @i TINYINT;
    DECLARE @str VARCHAR(100), @no VARCHAR(20), @unit VARCHAR(16);
    SELECT @str='', @no='另壹貳三肆伍陸柒捌玖', @unit='拾佰仟萬拾佰仟億';
    SET @temp = @num;
    SELECT @i=0, @res=@temp%10, @temp=@temp/10;

    WHILE @temp > 0
    BEGIN
        IF @i = 0 SET @str = SUBSTRING(@no, @res+1, 1);
        ELSE SET @str = SUBSTRING(@no, @res+1, 1) + SUBSTRING(@unit, @i, 1) + @str;
        SELECT @res = @temp%10, @temp = @temp/10;
        SET @i = @i + 1;
    END
    SET @str = SUBSTRING(@no, @res+1, 1) + SUBSTRING(@unit, @i, 1) + @str;

    -- 處理「另」字替換邏輯
    SET @str = REPLACE(@str, '另拾', '另');
    SET @str = REPLACE(@str, '另佰', '另');
    SET @str = REPLACE(@str, '另仟', '另');
    SET @str = REPLACE(@str, '另萬', '萬');
    WHILE @i > 0
    BEGIN
        SET @str = REPLACE(@str, '另另', '另');
        SET @i = CHARINDEX('另另', @str);
    END
    SET @str = REPLACE(@str, '另萬', '萬');
    SET @str = REPLACE(@str, '億萬', '億');
    IF RIGHT(@str, 1) = '另' SET @str = LEFT(@str, LEN(@str)-1);
    RETURN @str;
END;

-- 測試
SELECT dbo.Fun_CGNum(12345);       -- 壹萬貳仟三佰肆拾伍
SELECT dbo.Fun_CGNum(10023040);    -- 壹仟另貳萬三仟另肆拾

擴充建議：可進一步支援小數位 (元/角/分) 與負數處理。