为什么变量调用不到,有什么办法?解决方法

为什么变量调用不到,有什么办法?
先看看这个存储过程吧。因为我要根据@in_UserID来决定查询哪一个表，所以使用exec来执行一条SQL语句，但是执行时提示@out_State、@out_Type等变量不存在。要求一定要用变量来返回结果。

CREATE   PROCEDURE   [dbo].GetInfo
@in_UserID               int   ,
@out_State                 tinyint   output,
@out_Type           int   output
AS
declare   @SQLStr   varchar(200)
declare   @TableID   int
set   @TableID   =   @in_UserID/1000000
set   @SQLStr   =   'Select   top   1   @out_State   =UM_State,   @out_Type         =UM_Type   From   tbl_Info '+cast(@TableID   as   varchar(10))+ '   Where   (UM_UserID   =   @in_UserID) '
exec(@SQLStr)
GO




[解决办法]
--如何将exec执行结果放入变量中？

declare @num int, @sql nvarchar(4000)
set @sql= 'select @a=count(*) from tableName '
exec sp_executesql @sql,N '@a int output ',@num output
select @num

[解决办法]

CREATE PROCEDURE [dbo].GetInfo
@in_UserID int ,
@out_State tinyint output,
@out_Type int output
AS
declare @SQLStr nvarchar(4000) --注意这里为Nvarchar
declare @TableID int
set @TableID = @in_UserID/1000000


declare @state int,@type int

set @SQLStr = 'Select top 1 @a = UM_State, @b = UM_Type From tbl_Info '+cast(@TableID as nvarchar(10))+ ' Where (UM_UserID = ' + cast(@in_UserID as nvarchar(100)) + ') '

exec sp_executesql @SQLStr,N '@a int output,@b int output ',@state output ,@type output

set @out_State = @state
set @out_Type = @type

GO
[解决办法]
CREATE PROCEDURE [dbo].GetInfo
@in_UserID int ,
@out_State tinyint output,
@out_Type int output
AS
declare @SQLStr nvarchar(200)
declare @TableID int
set @TableID = @in_UserID/1000000
set @SQLStr = N 'Select top 1 @out_State =UM_State, @out_Type =UM_Type From tbl_Info '+cast(@TableID as varchar(10))+ ' Where (UM_UserID = '+cast(@in_UserID as varchar(10))+ ') '
exec sp_execute @SQLStr,N '@out_State tinyint output,@out_Type int output ',@out_State output,@out_Type output
GO

[解决办法]
--try
CREATE PROCEDURE [dbo].GetInfo
@in_UserID int ,
@out_State tinyint output,
@out_Type int output
AS
declare @SQLStr nvarchar(2000)
declare @TableID int
set @TableID = @in_UserID/1000000
set @SQLStr = 'Select top 1 @out_State =UM_State, @out_Type =UM_Type From tbl_Info '+cast(@TableID as varchar(10))+ ' Where (UM_UserID = @in_UserID) '
exec sp_executesql @SQLStr,N '@out_State tinyint out ,@out_Type int out,@in_UserID int ',@out_State out ,@out_Type out,@in_UserID
GO
[解决办法]
楼上的能用了,我还想问问有没有更好的方法,例如:
select * from getTablebyname( 'info_1 ')
====没有这种方法,表变量语句只能用动态SQL语法(EXEC SP_EXECUTESQL).
[解决办法]
函数中不能用动态SQL语句。