SQL Server存储过程执行成功但Student表无新增数据求助

针对你遇到的执行存储过程返回“1行受影响”但Student表无数据、直接INSERT也失败的问题，以下是几个常见原因及对应的解决方法：

1. 隐式事务未提交 #

若你的会话开启了隐式事务（SET IMPLICIT_TRANSACTIONS ON），INSERT操作后不会自动提交事务，数据仅存在于当前未提交的事务中，未持久化到数据库。即使存储过程返回受影响行数，未提交的情况下查询不到数据。

排查与修复： #

• 执行存储过程后，手动执行COMMIT TRANSACTION;，再查询Student表验证数据是否存在。
• 建议修改存储过程，添加显式事务控制，确保操作成功后提交，失败则回滚：

ALTER PROCEDURE dbo.AddNewStudent
    @StudentID VARCHAR(6),
    @TempPassword VARCHAR(100),
    @Name VARCHAR(100),
    @Phone VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        OPEN SYMMETRIC KEY PasswordEncryptionKey
        DECRYPTION BY CERTIFICATE AISServerCert;

        INSERT INTO dbo.Student (ID, SystemPwd, Name, Phone)
        VALUES (@StudentID, 
                EncryptByKey(Key_GUID('PasswordEncryptionKey'), @TempPassword),
                @Name, @Phone);

        CLOSE SYMMETRIC KEY PasswordEncryptionKey;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- 抛出错误便于排查问题
    END CATCH
END;

2. 权限不足导致操作隐式失败 #

虽然你给DBAdmins角色授予了存储过程的EXECUTE权限，但直接INSERT失败说明角色可能缺少Student表的INSERT权限；同时，存储过程中用到的对称密钥和证书也需要对应权限，否则加密操作可能失败，但存储过程可能因错误抑制返回虚假的受影响行数。

排查与修复： #

执行以下语句为DBAdmins角色授予必要权限：

-- 授予Student表的INSERT权限
GRANT INSERT ON dbo.Student TO DBAdmins;
-- 授予对称密钥的查看与控制权限
GRANT VIEW DEFINITION ON SYMMETRIC KEY::PasswordEncryptionKey TO DBAdmins;
GRANT CONTROL ON SYMMETRIC KEY::PasswordEncryptionKey TO DBAdmins;
-- 授予证书的查看权限
GRANT VIEW DEFINITION ON CERTIFICATE::AISServerCert TO DBAdmins;

3. 行级安全或触发器阻止数据可见/持久化 #

• 行级安全（RLS）：如果Student表配置了行级安全策略，可能限制DBAdmins用户仅能查看符合特定条件的数据，导致插入后无法查询到自己添加的记录。
• 触发器：若存在INSTEAD OF INSERT触发器，可能替换了原插入逻辑；或AFTER INSERT触发器执行了回滚操作，导致数据未真正写入数据库。

排查与修复： #

• 检查行级安全策略：

SELECT * FROM sys.security_policies WHERE object_id = OBJECT_ID('dbo.Student');

若存在策略，查看筛选规则，确保插入数据符合可见条件，或调整策略允许DBAdmins查看所有数据。

• 检查触发器：

SELECT * FROM sys.triggers WHERE parent_id = OBJECT_ID('dbo.Student');

若有触发器，查看其代码，确认是否存在回滚或修改插入数据的逻辑，按需调整。

4. 数据库上下文错误 #

执行存储过程和查询SELECT * FROM Student时，可能处于不同的数据库上下文（比如在TestDB执行存储过程，却切换到Master库查询），导致看不到数据。

排查与修复： #

• 查询当前数据库：

SELECT DB_NAME();

确保与执行存储过程时的数据库一致，或查询时指定完整表名：SELECT * FROM YourDatabaseName.dbo.Student;

内容的提问来源于stack exchange，提问作者Jason Low