SQL Server 常用預存程序大全：分頁、註冊、系統管理與實用工具

前言

在 SQL Server 開發與維護中，預存程序是提升效能、封裝邏輯的關鍵工具。本文整理多個實戰常用的預存程序，涵蓋分頁查詢、用戶註冊、系統管理、資料庫還原輔助、阿拉伯數字轉中文大寫等場景，供開發者參考調用。

一、通用分頁查詢預存程序

利用游標實現靈活分頁，支援任意 SQL 查詢語句作為資料來源。

CREATE PROCEDURE [dbo].[GetRecordSet]
    @strSql varchar(8000),  -- 查詢 SQL，例如：select * from [Users]
    @PageIndex int,         -- 當前頁碼
    @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, @PageCount;  -- 回傳總筆數與總頁數
    EXEC sp_cursorfetch @p1, 16, @currentPage, @PageSize;     -- 擷取當頁資料
    EXEC sp_cursorclose @p1;
    SET NOCOUNT OFF;
END
GO

二、用戶註冊預存程序

包含帳號、信箱唯一性檢查，自動產生 UserID 並寫入預設欄位。

CREATE PROC [dbo].[UserAdd]
    @loginID nvarchar(50),   -- 登入帳號
    @passWord nvarchar(50),  -- 密碼
    @email nvarchar(200)     -- 電子信箱
AS
BEGIN
    DECLARE @userID int;

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

    -- 檢查信箱是否已註冊
    ELSE IF EXISTS (SELECT email FROM tableName WHERE email = @email)
        RETURN -2;

    -- 註冊成功，寫入資料
    ELSE
    BEGIN
        SELECT @userID = ISNULL(MAX(userID), 100000) + 1 FROM tableName;
        INSERT INTO tableName (userID, loginID, [passWord], userName, linkNum, address, email, createTime, status)
        VALUES (@userID, @loginID, @passWord, '', '', '', @email, GETDATE(), 1);
        RETURN @userID;
    END
END

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

1. 擴充屬性管理（欄位說明）

-- 新增欄位描述
EXEC sp_addextendedproperty 'MS_Description', 'Employee ID', 'user', dbo, 'table', 'T2', 'column', 'id';
-- 修改欄位描述
EXEC sp_updateextendedproperty 'MS_Description', 'this is a test', 'user', dbo, 'table', 'T2', 'column', 'id';

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

EXEC sp_renamedb 'old_db_name', 'new_db_name';
EXEC sp_rename 'old_table_name', 'new_table_name';          -- 資料表
EXEC sp_rename 'table_name.[old_column_name]', 'new_column_name', 'COLUMN';  -- 欄位

3. 查看預存程序定義

EXEC sp_helptext 'sp_name';

4. 安全性相關（登入、角色、權限）

-- 建立 SQL 登入
EXEC sp_addlogin @loginame='', @passwd='', @defdb='', @deflanguage=NULL, @sid=NULL, @encryptopt=NULL;
-- 授與 Windows 登入存取權
EXEC sp_grantlogin @loginame='';
-- 刪除登入
EXEC sp_droplogin @loginame='';
-- 拒絕 Windows 登入
EXEC sp_denylogin @loginame='';
-- 撤銷 Windows 登入
EXEC sp_revokelogin @loginame='';
-- 變更預設資料庫/語言
EXEC sp_defaultdb @loginame='', @defdb='';
EXEC sp_defaultlanguage @loginame='', @language='';
-- 變更密碼
EXEC sp_password @old='', @new='', @loginame='';
-- 伺服器角色管理
EXEC sp_addsrvrolemember @loginame='', @rolename='';
EXEC sp_dropsrvrolemember @loginame='', @rolename='';
-- 資料庫使用者/角色/權限
EXEC sp_grantdbaccess @loginame='', @name_in_db=NULL;
EXEC sp_addrole @rolename='', @ownername='';
EXEC sp_addrolemember @rolename='', @membername='';
GRANT SELECT ON authors TO Limperator;
DENY SELECT ON authors TO Limperator;
REVOKE SELECT ON authors TO Limperator;

四、資料庫還原前強制斷開連線

還原資料庫前需將目標資料庫設為單人模式，以下程序可殺掉指定資料庫的所有作用中連線。

CREATE PROC killspid (@dbname varchar(20))
AS
BEGIN
    DECLARE @sql nvarchar(500), @spid int;
    SET @sql = '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
GO

用途：備份/還原/異動結構前，確保獨佔存取。

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

支援含零數字處理，輸出如「壹萬貳仟三佰肆拾伍」。

CREATE FUNCTION 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 = '拾佰仟萬拾佰仟億';

    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
GO

測試：

SELECT dbo.fun_cgnum(900000000), dbo.fun_cgnum(903002051), dbo.fun_cgnum(903002050);

註：若需支援小數位（元/角/分），可在此基礎上擴充。