drop proc mytestproceduer;
go
create PROCEDURE mytestproceduer AS
declare @serchwords char(10)='wen';
declare @serchtable char(20)='T_mytest';
declare @serchtablename char(800);
BEGIN
set @serchtablename='select * from '+ @serchtable +' where [C_name]='+@serchwords
END
exec (@serchtablename);
GO以上存储过程有为什么会报如下错误消息 207,级别 16,状态 1,第 1 行
列名 'wen' 无效。
go
create PROCEDURE mytestproceduer AS
declare @serchwords char(10)='wen';
declare @serchtable char(20)='T_mytest';
declare @serchtablename char(800);
BEGIN
set @serchtablename='select * from '+ @serchtable +' where [C_name]='+@serchwords
END
exec (@serchtablename);
GO以上存储过程有为什么会报如下错误消息 207,级别 16,状态 1,第 1 行
列名 'wen' 无效。
go
create PROCEDURE mytestproceduer AS
declare @serchwords char(10)='wen';
declare @serchtable char(20)='T_mytest';
declare @serchtablename char(800);
BEGIN
set @serchtablename='select * from '+ @serchtable +' where [C_name]='+@serchwords
print @serchtablename ---看看这里输出的是什么,估计是你的列给的不对在拼接字符串的时候
END
exec (@serchtablename);
GO
既然你的表名/列名都是在存储过程里定义的,为啥要写成动态查询呢?
直接:
select * from t_mytest where c_name='wen'
不好啊!
如果一定要这样,改成:
set @serchtablename='select * from '+ @serchtable +' where [C_name]=''''+@serchwords+''''
set @serchtablename='select * from '+ @serchtable +' where [C_name]='''+@serchwords+''''
declare @serchwords char(10);
declare @serchtable char(20);
declare @serchtablename char(800);
set @serchwords='wen';
set @serchtable ='T_mytest';BEGIN
set @serchtablename='select * from '+ convert(char(20),@serchtable) +' where [C_name]='+@serchwords
END
create PROCEDURE mytestproceduer AS
declare @serchwords char(10)='wen';
declare @serchtable char(20)='T_mytest';
declare @serchtablename char(800);
BEGIN
set @serchtablename='select * from '''+ @serchtable +''' where [C_name]='''+@serchwords+''''
END
exec (@serchtablename);
declare @serchwords char(10)='wen';
declare @serchtable char(20)='T_mytest';
declare @serchtablename char(800);
BEGIN
set @serchtablename='select * from '+ @serchtable +' where [C_name]='''+@serchwords+''''
END
exec (@serchtablename);
SET QUOTED_IDENTIFIER ON
GOdeclare @serchwords char(10)='wen';
declare @serchtable char(20)='T_mytest';
declare @serchtablename char(800);
这些都可以定义成varchar
set @serchtablename="select * from "+ @serchtable +" where [C_name]='"+@serchwords+"' "