declare
@user_id int,
@sysrole_id int,
@SqlString nvarchar(4000),
@TableName varchar(80),
@ParmDefinition nvarchar(4000),
@role_no1 varchar(50),
@TableName1 varchar(80)
set @TableName= 'dgerp.lsdata.DBO.fndrole '
set @SqlString=N 'select @sysrole_id=role_id from @TableName1 '+
N ' where [email protected]_no '
exec sp_executesql @SqlString ,N '@TableName1 varchar(80),@sysrole_id int output,@role_no nvarchar(50) ',
@[email protected],@role_no= 'SUPERVISOR ',@[email protected]_id output
select @user_id
總是報錯：
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@TableName1 '.
[email protected] ' 已經做了聲明了
請教各位大俠
------解决方案--------------------
LZ可以这样写
set @SqlString=N 'select @sysrole_id=role_id from ' + @TableName1 +
N ' where role_no= '[email protected]_no1+ ' '
exec sp_executesql @SqlString ,N '@sysrole_id int output ',@[email protected]_id output
select @user_id
------解决方案--------------------
表名和列名，都不能通过参数传递进去，只能在先串接好SQL串：
declare
@user_id int,
@sysrole_id int,
@SqlString nvarchar(4000),
@TableName varchar(80),
@ParmDefinition nvarchar(4000),
[email protected]_no1 varchar(50),
@role_no varchar(50)
[email protected] varchar(80)
set @TableName= 'dgerp.lsdata.DBO.fndrole '
set @SqlString=N 'select @sysrole_id=role_id from '[email protected]+ ' where [email protected]_no '
set @role_no= 'SUPERVISOR '
exec sp_executesql @SqlString ,N '@role_no nvarchar(50),@sysrole_id int output ',@role_no,@sysrole_id output
select @sysrole_id
--select @user_id
--我晕，一堆mistake
------解决方案--------------------
declare
@user_id int,
@sysrole_id int,
@SqlString nvarchar(4000),
@TableName varchar(80),
@ParmDefinition nvarchar(4000),
@role_no1 varchar(50),
@TableName1 varchar(80)
set @TableName= 'dgerp.lsdata.DBO.fndrole '
set @SqlString=N 'select @sysrole_id=role_id from @TableName1 '+
N ' where [email protected]_no '
exec sp_executesql @SqlString ,N '@TableName1 varchar(80),@sysrole_id int output,@role_no nvarchar(50) ',
@[email protected],@role_no= 'SUPERVISOR ',@[email protected]_id output
select @user_id
-----------------------------------------
把这句：set @SqlString=N 'select @sysrole_id=role_id from @TableName1 '+