存储过程中动态调用存储过程

[chunlai]

简单的说就是想根据不同的输入选择不同的存储过程同时也有参数带入.
谢谢.代码如下:

外部触发代码:
declare @SpNumber varchar(21)
declare @ShortMessage varchar(140)
declare @UserNumber varchar(21)

select @SpNumber='8809133'
select @ShortMessage='01'
select @UserNumber='13185061713'

exec Xp_CallDiffProc @UserNumber,@SpNumber,@ShortMessage

-----------Xp_CallDiffProc存储过程------------
CREATE PROCEDURE [dbo].[Xp_CallDiffProc]
(
@UserNumber varchar(21),
@SpNumber varchar(21),
@ShortMessage varchar(140)
)
AS

select @ShortMessage='01'
declare @exeSql NVARCHAR(50)

print 'Xp_CallDiffProc '+@UserNumber
print 'Xp_CallDiffProc '+@SpNumber
print 'Xp_CallDiffProc '+@ShortMessage

select @exeSql='exec scene'+@ShortMessage +N' @UserNumber,'+N'@SPNumber,'+N'@ShortMessage'
print @exeSql
--EXEC sp_executesql @exeSql
--exec (@exeSql)

-----------scene01存储过程------------

CREATE PROCEDURE [dbo].[scene01]
(
@UserNumber varchar(21),
@SpNumber varchar(21),
@ShortMessage varchar(140)
)
AS
print 'scene01 '+@UserNumber
print 'scene01 '+@SpNumber
print 'scene01 '+@ShortMessage

[netcoder]

用case when 语句吧
case 存储过程名字
when 'a'
执行a存储过程
when 'b'
执行b存储过程
when ……
end