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

SQL Server 實用存儲過程大全：分頁、註冊、系統管理與數字轉換

編輯：關於SqlServer

一、高效分頁存儲過程 (SQL 2000 風格，相容性佳)

利用游標 sp_cursoropen 實現伺服器端分頁，避免大量資料傳輸至應用層。


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

二、用戶註冊存儲過程 (含業務邏輯驗證)

封裝帳號/信箱唯一性檢查、自動生成 ID、預設值填充等邏輯，回傳新用戶 ID 或錯誤碼 (-1 帳號重複, -2 信箱重複)。


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 (簡易策略，生產環境建議用 IDENTITY 或 SEQUENCE)
    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;
END

三、常用系統存儲過程速查表

1. 文件說明與重命名


-- 為欄位加上說明
EXEC sp_addextendedproperty 'MS_Description', '員工編號', 'user', dbo, 'table', 'T2', 'column', 'id';
-- 修改說明
EXEC sp_updateextendedproperty 'MS_Description', '這是測試', 'user', dbo, 'table', 'T2', 'column', 'id';

-- 修改資料庫名稱
EXEC sp_renamedb 'old_db', 'new_db';
-- 修改資料表名稱
EXEC sp_rename 'old_table', 'new_table';
-- 修改欄位名稱
EXEC sp_rename 'table_name.old_col', 'new_col', 'COLUMN';

-- 查看存儲過程原始碼
EXEC sp_helptext 'sp_Name';

2. 安全性與權限管理 (核心帳號/角色/權限)


-- 登入帳號管理
EXEC sp_addlogin @loginame='user', @passwd='pwd', @defdb='master';  -- SQL 登入
EXEC sp_grantlogin @loginame='DOMAIN\user';                          -- Windows 登入
EXEC sp_droplogin @loginame='user';                                  -- 刪除登入

-- 資料庫用戶映射
EXEC sp_grantdbaccess @loginame='user', @name_in_db='db_user';       -- 建立用戶
EXEC sp_revokedbaccess @name_in_db='db_user';                        -- 移除用戶

-- 角色管理
EXEC sp_addrole @rolename='AppReadOnly';                             -- 建立角色
EXEC sp_addrolemember @rolename='AppReadOnly', @membername='db_user'; -- 加入成員

-- 權限控制 (GRANT / DENY / REVOKE)
GRANT SELECT ON [Authors] TO [AppReadOnly];
DENY DELETE ON [Authors] TO [AppReadOnly];
REVOKE SELECT ON [Authors] TO [AppReadOnly];

四、資料庫還原前殺掉連接進程

還原資料庫需獨佔存取，此 SP 可強制斷開指定資料庫的所有連線。


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

五、阿拉伯數字轉中文大寫函數 (支援萬/億單位)

輸入 12345 → 輸出 壹萬貳仟參佰肆拾伍；輸入 10023040 → 輸出 壹仟零貳萬參仟零肆拾。


CREATE FUNCTION [dbo].[fn_NumberToChinese] (@num INT)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @temp INT, @res INT, @i TINYINT;
    DECLARE @str VARCHAR(100), @chNum VARCHAR(20), @unit VARCHAR(16);
    SELECT @str='', @chNum='零壹貳參肆伍陸柒捌玖', @unit='拾佰仟萬拾佰仟億';
    SET @temp = @num;
    SELECT @i=0, @res=@temp%10, @temp=@temp/10;
    WHILE @temp > 0
    BEGIN
        IF @i = 0 SET @str = SUBSTRING(@chNum, @res+1, 1);
        ELSE SET @str = SUBSTRING(@chNum, @res+1, 1) + SUBSTRING(@unit, @i, 1) + @str;
        SELECT @res=@temp%10, @temp=@temp/10; SET @i=@i+1;
    END
    SET @str = SUBSTRING(@chNum, @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 CHARINDEX('零零', @str) > 0 SET @str = REPLACE(@str, '零零', '零');
    SET @str = REPLACE(@str, '零萬', '萬');
    SET @str = REPLACE(@str, '億萬', '億');
    IF RIGHT(@str, 1) = '零' SET @str = LEFT(@str, LEN(@str)-1);
    RETURN @str;
END

測試： SELECT dbo.fn_NumberToChinese(903002051) → 玖億零參佰萬零貳佰零伍拾壹