SQL Server 中 存储过程返回值问题

[xzjxylophone]

首先有如下的一个表
表名：EM
create table EM
(
[ID] int primary key,
Mes varchar(50),
Det varchar(50)
)
数据如下：
insert into EM([ID], Mes, Det)
values(1, "11", "111")
insert into EM([ID], Mes, Det)
values(2, "22", "222")
insert into EM([ID], Mes, Det)
values(3, "33", "333")

现在有如下的四个存储过程
存储过程一：
create procedure AAA1
@ID int
as
select * from EM where [ID] < @ID
return 0
存储过程二：
create procedure AAA2
as
insert into EM([ID], Mes, Det)
values(4, "444", "444")
return 0
存储过程三：
create procedure AAA3
@ID int
as
if not exists(select [ID] from EM where [ID] = @ID)
return 1987
update EM set Mes = "1111", Det = "111111" where [ID] = @ID
return 0

存储过程四：
create procedure AAA4
@ID int
as
declare @Flage int
begin transaction A1
exec('AAA1' + @ID)
exec('AAA2')
exec('AAA3' + @ID)

rollback transaction
commit transaction
return 0

现在我的问题是：
怎么得到存储过程的返回值 考虑的结果如下：
让 @Flage = exec('AAA1' + @ID)--（第一个存储过程） 如果不是0就回滚否则继续执行事务的下一步


同时还有这样一个问题
在存储过程二中 如果插入的记录是存在的话，怎么让存储过程四的事务回滚
希望有高手帮我解决下
谢谢了~~~

[you_tube]

sp_executesql 

[you_tube]

2.事务保存点

[xzjxylophone]

能详细点吗？

[you_tube]

1.得到存储过程的返回值

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

下班了，回去再看

[肥龙上天]

你这是在做什么啊？
把所有语句放到一个存储过程里面不行吗？

[肥龙上天]

create procedure AAA1 
@ID int 
as
begin
	set nocount on
	begin tran
        SET XACT_ABORT  on
		if exists(select 1 from em where id < @id )
			return 
		if not exists(select 1 from em where id = 4)
			insert into EM([ID], Mes, Det) values(4, '444', '444') 
		if exists(select [ID] from EM where [ID] = @ID) 
			update EM set Mes = '1111', Det = '111111' where [ID] = @ID 
	commit tran

end

[wlkjhxd]

EXEC @Flage = [AAA1] @ID --这样就可以得到返回值了

[xzjxylophone]

[Quote=引用 6 楼 yangsnow_rain_wind 的回复:]
SQL codecreate procedure AAA1
@ID int
as
begin
set nocount on
begin tran
SET XACT_ABORT on
if exists(select 1 from em where id < @id )
return
if not exists(select 1 from em where id = 4)
insert into EM([ID], Mes, Det) values(4, '444', '444')
if exists(select [ID] from EM where [ID] = @ID)
update EM set …
[/Quote]


我的一个存储过程很复杂，在这里我只是简化了

[xzjxylophone]

[Quote=引用 7 楼 wlkjhxd 的回复:]
SQL code
EXEC @Flage = [AAA1] @ID --这样就可以得到返回值了
[/Quote]

谢谢了
就是这样就可以了~~~