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
需要注意的地方是@cmd声明成nvarchar(4000)
--再举一例
if object_id('prc11') is not null
drop proc prc11
go
CREATE PROC PRC11(
@a varchar(16)=null,
@returnvalue int=null output
)
AS BEGIN
SET @returnvalue = LEN(@A) + 2234
END
go
declare @cmd Nvarchar(1000),@r int
set @r = 0
set @cmd = 'exec PRC11 @a=''myname11'',@returnvalue=@r output'
exec sp_executesql @cmd ,N'@r int output',@r output
SELECT @R
drop proc prc11
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1365283
