if(@docount=1)
select count(id) from jplp where @sfield like '%'+@skey+'%'
-------------------------
@sfield like '%'+@skey+'%'
的 @sfield
select count(id) from jplp where @sfield like '%'+@skey+'%'
-------------------------
@sfield like '%'+@skey+'%'
的 @sfield
解决方案 »
- on 和 Where 区别
- 为何无法向char字段中同时输入汉字和标点符号?(字段长度为10)
- 请大家帮忙怎么写这个查询,非常感谢!
- 没有符合条件的记录但是也不为null怎么办
- 怎么用SQL语句将一个数据库表中的内容复制到另外一个数据库的表中
- 存储过程在pb中和在SQL中执行为什么不一样?急!
- 如何改变SQL的数据文件保存路径? 急!!!
- 库存明细表的sql如何写
- 请教一个老问题: 在SQL SERVER 70 中, 用什么语句设置行级锁?要求是: 别人不能读, 也不能写。 另外, 该如何释放?
- 如何生成类似手机冲值卡密码的不重复16位数字?
- 急等:取值
- 如何根据日期从另一个表取数UPDATE到本表(非精确日期匹配)?
@sfield nvarchar(20),
@skey nvarchar(20),
@pagesize int,
@pageindex int,
@docount bit)
as
begin
set nocount on
if(@docount=1)
exec('select count(id) from jplp where '+@sfield+' like ''%'+@skey+'%''')
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
exec('insert into '+@indextable+'(nid) select Id from jplp where '+@sfield+' like '''%'+@skey+'%''' order by fbsj desc
select O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj from jplp O,'+@indextable+' t where O.id=t.nid
and t.id>'+rtrim(@PageLowerBound)+' and t.id<='+rtrim(@PageUpperBound)+' order by t.id DESC')
end
set nocount off
end
GO
select count(id) from jplp where @sfield like '%'+@skey+'%'
-------------------------------
---〉
if(@docount=1)
declare @sqlTmp varchar(20)
set @sqlTmp =
'select count(id) from jplp where '+@sfield+ ' like ''%''+@skey+''%'''
exec(@sqlTmp)
改成 exec('select count(id) from jplp where'+ @sfield +'like ''%'+@skey+'%''' )
select count(id) from jplp where @sfield like '%'+@skey+'%'
CREATE procedure JplpGetListBySrh(
@sfield nvarchar (20),
@skey nvarchar (20),
@pagesize int,
@pageindex int,
@docount bit
)
as
set nocount on
if(@docount=1)
exec('select count(id) from jplp where '+ @sfield + ' like '''%'''+@skey+'''%'''')
else
begin
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 Id from jplp where '+@sfield+ ' like '''%'''+@skey+'''%' order by fbsj desc')
select O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj from jplp O,@indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id DESC
end
set nocount off
GO
select count(id) from jplp where @sfield like '%'+@skey+'%' ......
用EXEC(.....)
select O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj from jplp O,'+@indextable+' t where O.id=t.nid
and t.id>'+rtrim(@PageLowerBound)+' and t.id<='+rtrim(@PageUpperBound)+' order by t.id DESC')
这个语句还有问题
*=======================================
*Author: Amour
*Description: JplpGetListBySrh
*Released: 2006-2-16 16:53:02
*=======================================
*/
CREATE procedure JplpGetListBySrh(
@sfield nvarchar (20),
@skey nvarchar (20),
@pagesize int,
@pageindex int,
@docount bit
)
as
set nocount on
if(@docount=1)
exec('select count(id) from jplp where '+@sfield+' like ''%'+@skey+'%''')
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
------下面这行有错
exec('insert into @indextable (nid) select Id from jplp where '+@sfield+' like ''% '+@skey+' %'' order by fbsj desc')
------上面这行有错
select O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj from jplp O,@indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id DESC
end
set nocount off
GO
执行出现以下错误服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@indextable'。
到现在我也不知道有什么好方法
我遇到这种情况的时候
就是声明 create 一个表放在库中
用完了 再drop 就像你原来写的那样CREATE procedure JplpGetListBySrh(
@sfield nvarchar (20),
@skey nvarchar (20),
@pagesize int,
@pageindex int,
@docount bit
)
as
set nocount on
if(@docount=1)
exec('select count(id) from jplp where '+@sfield+' like ''%'+@skey+'%''')
else
begin
--create
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 Id from jplp where '+@sfield+' like ''% '+@skey+' %'' order by fbsj desc') select O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj from jplp O,indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id DESC
end
--加一句 drop
drop table indextable set nocount off
GO试一下 , 看看 还有什么问题吗???
select O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj from jplp O,indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id DESC
这句话没结果咯
我看了indextable里面的数据是空的
@sfield nvarchar (20),
@skey nvarchar (20),
@pagesize int,
@pageindex int,
@docount bit
)
as
set nocount on
if(@docount=1)
exec('select count(id) from jplp where '+@sfield+' like ''%'+@skey+'%''')
else
begin
--create
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBoundselect Id from jplp where '+@sfield+' like ''% '+@skey+' %'' order by fbsj desc
set nocount off
GO
@sfield nvarchar (20),
@skey nvarchar (20),
@pagesize int,
@pageindex int,
@docount bit
)
as
set nocount on
if(@docount=1)
exec('select count(id) from jplp where '+@sfield+' like ''%'+@skey+'%''')
else
begin
if (@pageindex=1)
exec('SELECT TOP '+@pagesize+' O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj FROM Jplp O WHERE '+@sfield+' like ''%'+@skey+'%'' ORDER BY ID')
else
declare @pagecount int
set @pagecount=@pagesize*(@pageindex-1)
exec('SELECT TOP '+@pagesize+' O.id,O.UserName,O.Cata,O.XiaoQm,O.JiaG,O.Lxr,O.Tel,O.Sh,O.Tj,O.Fbsj FROM Jplp O WHERE '+@sfield+' like ''%'+@skey+'%'' and (ID > (SELECT MAX(id) FROM (SELECT TOP '+@pagecount+' id FROM Jplp where '+@sfield+' like ''%'+@skey+'%'' ORDER BY id) AS T)) ORDER BY ID')
end
set nocount off
GO