存储过程问题 !恳请指教!
cmd=new SqlCommand("newsSearch",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@Board",board);
cmd.Parameters.Add("@KeyWord1",keyword1);
cmd.Parameters.Add("@KeyWord2",keyword2);
cmd.Parameters.Add("@pageindex",pager.CurrentPageIndex);
cmd.Parameters.Add("@pagesize",pager.PageSize);
cmd.Parameters.Add("@docount",false);
conn.Open();
repList.DataSource=cmd.ExecuteReader();
repList.DataBind();
conn.Close();
存储过程
CREATE procedure newsSearch
(@Board VarChar,
@KeyWord1 VarChar,
@KeyWord2 VarChar,
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(ID) from News where (board like '%"+@board+"%') and ((title like '%"+@keyword1+"%') or (content like '%"+@keyword2+"%'))
else
begin
declare @indextable table(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
insert into @indextable(nid) select ID from News where (board like '%"+@board+"%') and ((title like '%"+@keyword1+"%') or (content like '%"+@keyword2+"%')) order by SendTime desc
select O.ID,O.Title,O.SendTime from News O,@indextable t where O.ID=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
GO
我用的是webdiyer的分页控件
为什么即使把上面代码中的参数KeyWord1,KeyWord2,Board换成"s","s","%"还是能返回任何数据 但是将存储过程中的查询语句换成 select ID from News where (board like '%') and ((title like '%s%') or (content like '%s%')) order by SendTime desc
就可以正确返回数据呢
时间仓促,不能祥说!
恳请指教啊