EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。下一计数 = 0，当前计数 = 1。分析
日期：2014-05-16 浏览次数：20386 次
ALTER PROCEDURE [dbo].[cor_InsertServerConfig]
?(
?@ServerName varchar(20),
?@ServerIP varchar(20),
?@ServerCategory varchar(25),
?@ServerState? char(1),
?@ServerAbility int
?--@ID? int output
??
?)
AS
?DECLARE @ID INT
BEGIN transaction
?
?
?SET NOCOUNT ON;
??? IF EXISTS? (? SELECT 1 FROM [ServerConfig] WHERE ServerName =? @ServerName)
??? BEGIN
???? --return 0
?--SELECT @ID = 0
? SET @ID = 0
????RETURN @ID
?END
???
?ELSE
?BEGIN
?
?INSERT INTO [ServerConfig](ServerName,ServerIP,ServerCategory,ServerState,ServerAbility)
?VALUES (@ServerName,@ServerIP,@ServerCategory,@ServerState,@ServerAbility)
?
?--SELECT?ID FROM INSERTED? --C#里'INSERTED'对象名无效
? --SELECT SCOPE_IDENTITY()
?
? set @ID = @@identity
? commit transaction
? ---select @ID?非Output，而是return，用select 即使成功插入返回的也为0?
? return @ID?
?
END
?
当SELECT 1 FROM [ServerConfig] WHERE ServerName =? @ServerName时出现上面的错误。
?
Begin Tran
....
Commit Tran
?
中间不能出现 return，因为BEGIN TRANSACTION 语句将 @@TranCount加 1。ROLLBACK TRANSACTION 将 @@TranCount递减到 0,? return 使执行进程返回，但并没有使事务计数器减一，所以出现了语句执行后事务计数器出现不一致的情况....
?
?
?
ALTER PROCEDURE [dbo].[cor_InsertServerConfig]
?(
?@ServerName varchar(20),
?@ServerIP varchar(20),
?@ServerCategory varchar(25),
?@ServerState? char(1),
?@ServerAbility int
?--@ID? int output
??
?)
AS
?DECLARE @ID INT
BEGIN transaction
?
?
?SET NOCOUNT ON;
??? IF EXISTS? (? SELECT 1 FROM [ServerConfig] WHERE ServerName =? @ServerName)
??? BEGIN
???? --return 0
?--SELECT @ID = 0
? SET @ID = 0
???? ROLLBACK TRANSACTION
??RETURN @ID
?END
???
?ELSE
?BEGIN
?
?INSERT INTO [ServerConfig](ServerName,ServerIP,ServerCategory,ServerState,ServerAbility)
?VALUES (@ServerName,@ServerIP,@ServerCategory,@ServerState,@ServerAbility)
?
?--SELECT?ID FROM INSERTED? --C#里'INSERTED'对象名无效
? --SELECT SCOPE_IDENTITY()
?
? set @ID = @@identity
? commit transaction
? ---select @ID?非Output，而是return，用select 即使成功插入返回的也为0?
? return @ID?
?
???
?
END
?
?
这样就正确了。
?
另外这种情况
?
ALTER PROCEDURE [dbo].[sp_test]
AS
BEGIN
??? SET NOCOUNT ON;
??? set?? xact_abort?? on
--开始显式声明事务
??? begin?? tran
