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