ALTER PROCEDURE [dbo].[sp_paginationJquery]
(
@recordcount int=0 out,
@pagecount int=0 out,
@tablename varchar(50), --
@fieldlist varchar(500)='*', --
@orderfield varchar(200)='', --
@orderType varchar(200)='',--
@pagesize int=10, --
@pageindex int=0, --
@docount bit=0, --, 0
@strwhere nvarchar(4000)='1=1' -- (: where)
)
AS SET NOCOUNT ON
declare @sqlstr nvarchar(4000),@rnum int
set @sqlstr = ''
set @sqlstr = N' select @recordcount = Count(*) from '+@tablename+' where '+@strwhere
exec sp_executesql @sqlstr,N' @recordcount int output',@recordcount output
if @recordcount>0 begin
set @pagecount = (@recordcount + @pagesize -1)/@pagesize
end
else
begin
set @pagecount = 0;
end if @doCount != 0 -----------------------------------------------
begin
return
end
else -------------------------------------------------------------------
begin
set @rnum=@recordcount-@pagesize*(@pageindex -1)
if @rnum>0
begin
set @sqlstr='select top '+cast(@pagesize as varchar)+' '+@fieldlist+' from (
select top 100 percent * from (
select top '+cast(@rnum as varchar)+' * from '+@tablename+
' where '+@strwhere+' order by '+@orderfield+' ' +@ordertype+'
) as b order by '+@orderfield+' ' +@ordertype+'
) as a order by '+@orderfield+' ' +@ordertype+''
end
print @sqlstr
exec(@sqlstr)
end
return 这是一个分页的存储过程,奇怪的是当能查询到数据时执行起来没问题,但如果查询不到任何记录时会提示下面的错误信息
select @recordcount = Count(*) from tbsyitem_0611_del where mandt='9100'
消息 137,级别 15,状态 1,第 1 行
Must declare the scalar variable "@recordcount". (1 行受影响) (1 行受影响)
有谁能为我解决下吗??
(
@recordcount int=0 out,
@pagecount int=0 out,
@tablename varchar(50), --
@fieldlist varchar(500)='*', --
@orderfield varchar(200)='', --
@orderType varchar(200)='',--
@pagesize int=10, --
@pageindex int=0, --
@docount bit=0, --, 0
@strwhere nvarchar(4000)='1=1' -- (: where)
)
AS SET NOCOUNT ON
declare @sqlstr nvarchar(4000),@rnum int
set @sqlstr = ''
set @sqlstr = N' select @recordcount = Count(*) from '+@tablename+' where '+@strwhere
exec sp_executesql @sqlstr,N' @recordcount int output',@recordcount output
if @recordcount>0 begin
set @pagecount = (@recordcount + @pagesize -1)/@pagesize
end
else
begin
set @pagecount = 0;
end if @doCount != 0 -----------------------------------------------
begin
return
end
else -------------------------------------------------------------------
begin
set @rnum=@recordcount-@pagesize*(@pageindex -1)
if @rnum>0
begin
set @sqlstr='select top '+cast(@pagesize as varchar)+' '+@fieldlist+' from (
select top 100 percent * from (
select top '+cast(@rnum as varchar)+' * from '+@tablename+
' where '+@strwhere+' order by '+@orderfield+' ' +@ordertype+'
) as b order by '+@orderfield+' ' +@ordertype+'
) as a order by '+@orderfield+' ' +@ordertype+''
end
print @sqlstr
exec(@sqlstr)
end
return 这是一个分页的存储过程,奇怪的是当能查询到数据时执行起来没问题,但如果查询不到任何记录时会提示下面的错误信息
select @recordcount = Count(*) from tbsyitem_0611_del where mandt='9100'
消息 137,级别 15,状态 1,第 1 行
Must declare the scalar variable "@recordcount". (1 行受影响) (1 行受影响)
有谁能为我解决下吗??
SP_EXECUTESQL
N'select @recordcount = Count(*) from tbsyitem_0611_del where mandt='9'100'' ',
N'DECLARE @recordcount INT',@count OUTPUT
set @sqlstr = ''
set @sqlstr = N' select @recordcount = ISNULL(Count(*),0) from '+@tablename+' where '+@strwhere
exec sp_executesql @sqlstr,N' @recordcount int output',@recordcount output 加这样处理看看,
select @recordcount = ISNULL(Count(*),0) from tbsyitem_0611_del where mandt='9100'
select @recordcount = ISNULL(Count(*),0) from tbsyitem_0611_del where mandt='9100'
消息 137,级别 15,状态 1,第 1 行
Must declare the scalar variable "@recordcount".(1 行受影响)(1 行受影响)
这是我执行时SQL返回给我的所有结果,我纳闷的事为什么搜到记录时就不会报错呢??
set @sqlstr = '' set @sqlstr = N' select @recordcount = Count(*) from '+@tablename+' where '+@strwhere
exec sp_executesql @sqlstr,N' @recordcount int output',@recordcount output 这样应该没问题了
还少了个INT