百分求分类分页存储过程
表如下:
CREATE TABLE Users (
UniqueID int IDENTITY (1, 1) NOT NULL ,
RegDate datetime,
TrainField nvarchar(100)
)GO
要求的存储过程大体上如下:
CREATE PROCEDURE GetList
(
@PageSize int, --页大小,每页记录条数
@PageIndex, --要求的查询结果页面,如第1页、第2页、第@PageIndex页
@TrainField --如果不为空,则按照@TrainField进行分类查询
)
AS ...--返回结果为:select *
要求按照RegDate降序,TrainField类别进行分类分页。
对SQL了解不多,但任务比较紧,没有时间了,给出可用存储过程马上给分。
表如下:
CREATE TABLE Users (
UniqueID int IDENTITY (1, 1) NOT NULL ,
RegDate datetime,
TrainField nvarchar(100)
)GO
要求的存储过程大体上如下:
CREATE PROCEDURE GetList
(
@PageSize int, --页大小,每页记录条数
@PageIndex, --要求的查询结果页面,如第1页、第2页、第@PageIndex页
@TrainField --如果不为空,则按照@TrainField进行分类查询
)
AS ...--返回结果为:select *
要求按照RegDate降序,TrainField类别进行分类分页。
对SQL了解不多,但任务比较紧,没有时间了,给出可用存储过程马上给分。
create PROC GetPageData
(
@PageNumINT,--页号
@PageSizeINT,--每页记录数
@OrderByNVARCHAR(255) = 'flcode',--排序条件
@WhereNVARCHAR(1024) = '1=1'--查询条件
)
AS--按条件拼SQL语句
DECLARE @sql NVARCHAR(2048)
--临时变量,按给定的页号和分页大小,计算出的需查询的总记录条数
DECLARE @Total INT
--表中的记录总数
DECLARE @Count INTSELECT @Count = COUNT(*) FROM flower--判断参数 @PageSize, @PageNum是否有效
IF @PageSize <= 0
BEGIN
SET @PageSize = 1
ENDIF @PageNum <= 0
BEGIN
SET @PageNum = 1
END--输入的页号 @PageNum 有可能不在有效范围内。将其设定为有效值
IF @PageNum > (@Count / @PageSize)
BEGIN
SET @PageNum = (@Count / @PageSize)
IF @Count % @PageSize != 0
BEGIN
SET @PageNum = @PageNum + 1
END
END--计算 @Total
SET @Total = @PageSize * @PageNum
IF @Total > @Count
BEGIN
SET @PageSize = @Count - ((@Count / @PageSize) * @PageSize)
SET @Total = @Count
END
/*
--通过前面的计算, @PageSize 有可能是 0。此时将它改变为1,避免后续计算的错误
IF @PageSize <= 0
BEGIN
SET @PageSize = 1
END
*/
--拼接并执行 SQL 语句
SET @sql = 'SELECT * FROM (SELECT TOP ' + CAST(@PageSize AS VARCHAR(10)) +
' * FROM (SELECT TOP ' +
CAST(@Total AS VARCHAR(10)) +
' * FROM flower WHERE ' + @Where + ' ORDER BY ' + @OrderBy + ' ASC)
AS A WHERE ' + @Where + ' ORDER BY ' + @OrderBy + ' DESC) AS flower ORDER BY ' + @OrderBy
PRINT @sql
EXEC sp_executesql @sqlGO表名"flower"查找替换你自己的...
ALTER PROCEDURE Proc_Query
@PageIndex INT, --页面索引,从datagrid中获取
@PageSize INT, --页面显示数量,从datagrid中获取
@RecordCount INT, --返回记录总数
@PageCount INT, --返回分页后页数
@strGetFields nvarchar(1000), -- 需要查询的列
@tableName nvarchar(500) , --表名称
@ID nvarchar(100), --主键,(为表的主键)多个主键的话选择一个就好了
@strWhere nvarchar(1000) ='', -- 查询条件 (注意: 不要加 where)
@sortName nvarchar(50) =' asc ' , --排序方式
@orderName nvarchar(100), --Order by 子句,为主键中的一个列
@Sum_Columns nvarchar(100) = '' --合计的列
AS--调用例子:Exec Report_Query 0,30,30,1,'*','销售单明细','帐套代码,单号,序号','','','单号'
declare @Sum_JE numeric(16,4)
set @Sum_JE = 0
Declare @Get_HJJE nvarchar(1000)declare @countSelect nvarchar(2000)
--设置统计查询语句
if len(@strWhere) =0
--如果没有查询条件
begin
set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName
--*****************************************************************************
--得到合计金额
if len(@Sum_Columns) = 0
begin
set @Sum_JE = 0
end
else
begin
set @Get_HJJE=N'SELECT @ReturnHJJE = sum(' + @Sum_Columns + ') FROM '+@tableName
end
--*****************************************************************************
end
else
--否则
begin
set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName+' where '+@strWhere
--*****************************************************************************
--得到合计金额
if len(@Sum_Columns) = 0
begin
set @Sum_JE = 0
end
else
begin
set @Get_HJJE=N'SELECT @ReturnHJJE = sum(' + @Sum_Columns + ') FROM '+@tableName+' where '+@strWhere
end
--*****************************************************************************
end
SET NOCOUNT ON
--执行并返回总数
begin tran
begin
exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output
end
begin
exec sp_executesql @Get_HJJE,N'@ReturnHJJE numeric(16,4) output',@Sum_JE output--
endSET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)DECLARE @SQLSTR NVARCHAR(3000)
--实际总共的页码小于当前页码 或者 最大页码
if @PageCount>=0
--如果分页后页数大于0
begin
if @PageCount<=@PageIndex and @PageCount>0 --如果实际总共的页数小于datagrid索引的页数
--or @PageCount=1
begin
--设置为最后一页
set @PageIndex=@PageCount-1
end
else if @PageCount<=@PageIndex and @PageCount=0
begin
set @PageIndex=0;
end
end
--如果用普通的sql而不使用存储过程调用
declare @ID_temp varchar(100)
set @ID_temp='cast('+@ID+' as nvarchar(100)) '
declare @returnValue nvarchar(100)
set @returnValue=','''+cast(@PageCount as nvarchar(100))+''' as [总页数]' + ','''+ cast(@RecordCount as nvarchar(100))+''' as [总行数]'+ ','''+ cast(@Sum_JE as nvarchar(100))+''' as [总金额]'
--如果用普通的sql而不使用存储过程调用
IF @PageIndex = 0 OR @PageCount <= 1 --如果为第一页
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName
end
else
begin
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName
end
end
ELSE IF @PageIndex = @PageCount - 1 --如果为最后一页
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N' SELECT '+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+'ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName
end
else begin
SET @SQLSTR =N' SELECT '+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+'ORDER BY '+@orderName+@sortName+' ) and '+@strWhere+' ORDER BY '+@orderName+@sortName
end
end
ELSE --否则执行
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName
end
else
begin
SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName
end
end
EXEC (@SQLSTR)
set nocount off
commit tran