if object_id('pro1') is not null drop proc pro1 if object_id('pro2') is not null drop proc pro2 if object_id('tbtest') is not null drop table tbtest GO create table tbtest(id int identity(1,1),name varchar(20)) insert tbtest(name) select 'x' union all select 'myname' union all select 'myname' union all select 'yourname' union all select 'myname' select * from tbtest GO ----创建存储过程1 create proc pro1 @a varchar(16) = null,@returnvalue int=null output as select @returnvalue = max(id) from tbtest where name = @a GO ----创建存储过程2(在该存储过程中调用存储过程1) create proc pro2 @cmd nvarchar(4000) as declare @r int set @r = 0 exec sp_executesql @cmd,N'@r int output',@r output select @r GO----调用存储过程2 declare @cmd Nvarchar(4000) --set @cmd = 'exec pro1 ''myname'',@r output' set @cmd = 'exec pro1 @a=''myname'',@returnvalue=@r output'--效果同上一条语句 exec pro2 @cmd----清除测试环境 drop proc pro1,pro2 drop table tbtest
drop proc pro1
if object_id('pro2') is not null
drop proc pro2
if object_id('tbtest') is not null
drop table tbtest
GO
create table tbtest(id int identity(1,1),name varchar(20))
insert tbtest(name)
select 'x' union all
select 'myname' union all
select 'myname' union all
select 'yourname' union all
select 'myname'
select * from tbtest
GO
----创建存储过程1
create proc pro1 @a varchar(16) = null,@returnvalue int=null output
as
select @returnvalue = max(id) from tbtest where name = @a
GO
----创建存储过程2(在该存储过程中调用存储过程1)
create proc pro2 @cmd nvarchar(4000)
as
declare @r int
set @r = 0
exec sp_executesql @cmd,N'@r int output',@r output
select @r
GO----调用存储过程2
declare @cmd Nvarchar(4000)
--set @cmd = 'exec pro1 ''myname'',@r output'
set @cmd = 'exec pro1 @a=''myname'',@returnvalue=@r output'--效果同上一条语句
exec pro2 @cmd----清除测试环境
drop proc pro1,pro2
drop table tbtest