两个自定义存储过程,嵌套使用返回值的问题
create proc jfe @kmdm varchar(20),@yf varchar(2)
as
declare @sqltxt nvarchar(4000),
@sum decimal(18,2)
set @sqlTxt= 'select @sum=sum(isnull(j,0)) from pz '+@yf+ ' where kmdm like ' ' '+@kmdm+ '% ' ' '
exec sp_executesql @sqlTxt, N '@sum decimal(18,2) output ', @sum output
set @sum=isnull(@sum,0)
select @Sum
create proc jje @kmdm varchar(20),@yf varchar(2)
as
declare @sqltxt nvarchar(4000),
@sum decimal(18,2),
@ye decimal(18,2),
@ye1 decimal(18,2),
@ye2 decimal(18,2)
select @ye1=0,@ye2=0,@sum=0
exec @ye1=dbo.jfe @kmdm,@yf
--把传入的参数再传递到dbo.jfe 应该可以吧?
--在调试里面执行看到dbo.jfe最后是运算到一个值(非0),但为什么未把这个值返回到调用的存储过程里面呢?
--@ye1的值仍是0
set @ye=@ye1
print @ye
我现在是用临时表的方法解决的,比较麻烦,请问这里是怎么回事情呢?
[解决办法]
select @Sum=======> return @Sum
[解决办法]
刚找一个例子：楼主看看
reate table test(id int identity(1,1),code varchar(8))
insert into test select 'aaaa ' union select 'bbbb '
go
create procedure sp_test2
@id int output,
@code varchar(8) output
as
begin
select @id=id,@code=code from test where code= 'aaaa '
return
end
go
create procedure sp_test1
as
begin
declare @id int,@code varchar(8)
exec sp_test2 @id out,@code out
select @id as nid,@code as ncode
end
go
exec sp_test1
go
/*
nid ncode
----------- --------
1 aaaa
*/
drop procedure sp_test1,sp_test2
drop table test
