在存储过程中需要用到模糊查询,能够调试的起,但是无法用程序执行,报“列名无效”错误下面是存储过程片断,能够调试,但不能在程序中执行
declare @str nvarchar(4000)set @str='and info_content like ''%发热%'' and info_content like ''%咽痛%'' )'
create table #indextable(id int identity(1,1),nid int)
exec('insert into #indextable(nid) select b.info_id from kk_info b inner join kk_info_class a
on a.infoclass_id=b.info_classid
where (a.infoclass_parent = 2 or a.infoclass_parent in (
select infoclass_id from kk_info_class where infoclass_parent = 2))
and b.info_id in (select info_super_name from kk_info_sub
where sort_id=2 ' + @str)select * from #indextabledrop table #indextable程序片断
SqlCommand cmd = new SqlCommand("getDiseaseBySymptom",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@pageindex",1);
SqlParameter p2 = new SqlParameter("@pagesize",34);
SqlParameter p3 = new SqlParameter("@str",sql);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3); SqlDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);这一步就报无效列名错误,出在这个参数:@str,好像是这个参数的值有问题,因为要做模糊查询,所以用了like,加了单引号,因为查询的内容不固定,必须要在外面组合查询语句,不知道是不是单引号的问题
declare @str nvarchar(4000)set @str='and info_content like ''%发热%'' and info_content like ''%咽痛%'' )'
create table #indextable(id int identity(1,1),nid int)
exec('insert into #indextable(nid) select b.info_id from kk_info b inner join kk_info_class a
on a.infoclass_id=b.info_classid
where (a.infoclass_parent = 2 or a.infoclass_parent in (
select infoclass_id from kk_info_class where infoclass_parent = 2))
and b.info_id in (select info_super_name from kk_info_sub
where sort_id=2 ' + @str)select * from #indextabledrop table #indextable程序片断
SqlCommand cmd = new SqlCommand("getDiseaseBySymptom",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@pageindex",1);
SqlParameter p2 = new SqlParameter("@pagesize",34);
SqlParameter p3 = new SqlParameter("@str",sql);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3); SqlDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);这一步就报无效列名错误,出在这个参数:@str,好像是这个参数的值有问题,因为要做模糊查询,所以用了like,加了单引号,因为查询的内容不固定,必须要在外面组合查询语句,不知道是不是单引号的问题
declare @str nvarchar(4000)set @str='and info_content like ''%发热%'' and info_content like ''%咽痛%'' )'
create table #indextable(id int identity(1,1),nid int)
exec('insert into #indextable(nid) select b.info_id from kk_info b inner join kk_info_class a
on a.infoclass_id=b.info_classid
where (a.infoclass_parent = 2 or a.infoclass_parent in (
select infoclass_id from kk_info_class where infoclass_parent = 2))
and b.info_id in (select info_super_name from kk_info_sub
where sort_id=2 ' + @str)select * from #indextabledrop table #indextable
这是存储过程 我能够调试,因为不能传数据库,但是在查询分析器中能够执行的
{
conn.Open();
}
SqlCommand cmd = new SqlCommand("getDiseaseBySymptom",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@pageindex",1);
SqlParameter p2 = new SqlParameter("@pagesize",34);
SqlParameter p3 = new SqlParameter("@str",sql);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3); SqlDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
这是程序,执行到最后就报无效列名错
这边的字段没有批定是那个表的。
(
@pageindex int,
@pagesize int,
@str ntext
)
as
set nocount on
create table #indextable(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
exec('insert into #indextable(nid) select b.info_id from kk_info b inner join kk_info_class a
on a.infoclass_id=b.info_classid
where (a.infoclass_parent = 2 or a.infoclass_parent in (
select infoclass_id from kk_info_class where infoclass_parent = 2))
and b.info_id in (select info_super_name from kk_info_sub
where sort_id=2 ' + @str)
select info_id, info_name from kk_info a, #indextable t where
a.info_id = t.nid and t.id>@PageLowerBound and t.id <= @PageUpperBound order by t.id
drop table #indextable
set nocount off--- exec getDiseaseBySymptom 1,10,'and info_content like ''%发热%'' and info_content like ''%咽痛%'' )'这是存储过程