FineUI 官方论坛

标题: 分页存储过程 [打印本页]

作者: leetle 时间: 2013-11-19 09:31
标题: 分页存储过程
本帖最后由 leetle 于 2013-11-19 09:34 编辑

/****** Object: StoredProcedure [dbo].[GETRECORDFROMPAGE] Script Date: 11/19/2013 09:30:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*************************************
分页存储过程——SQL2005
CREATEON:2013-04-27
CREATEBY

YJ
*************************************/
ALTER PROCEDURE [dbo].[GETRECORDFROMPAGE]
(
@TBLNAME VARCHAR(1000),          --表名
@PRIMARYCOLUMNS VARCHAR(100), --主键(PRIMARYKEYS)
@PAGESIZE INT = 10,
@PAGEINDEX INT = 1,
@ORDERTYPE BIT=0,             --排序方式(1：DESC;0:ASC)
@STRWHERE NVARCHAR(MAX) = NULL, --条件
@ROWTOTAL INT=0 OUTPUT
)
AS

DECLARE @STRFILTER NVARCHAR(4000),@SORDER VARCHAR(100)
DECLARE @SQL NVARCHAR(4000)

IF @STRWHERE IS NOT NULL AND @STRWHERE != ''
BEGIN
      SET @STRFILTER = ' WHERE ' + @STRWHERE + ' '
END
ELSE
BEGIN
      SET @STRWHERE = '1=1'
      SET @STRFILTER = ''
END

SET @SQL='SELECT @ROWTOTAL=COUNT(0) FROM '+@TBLNAME + @STRFILTER
EXEC SP_EXECUTESQL @SQL,N'@ROWTOTAL INT OUT',@ROWTOTAL OUT

BEGIN
IF(@ORDERTYPE=1)
SET @SORDER = @PRIMARYCOLUMNS + ' DESC '
ELSE
SET @SORDER=@PRIMARYCOLUMNS+' ASC '

IF @PAGEINDEX < 1
SET @PAGEINDEX = 1
BEGIN
      DECLARE @START_ID VARCHAR(50)
      DECLARE @END_ID VARCHAR(50)
      SET @START_ID = CONVERT(VARCHAR(50),(@PAGEINDEX - 1) * @PAGESIZE + 1)
      SET @END_ID = CONVERT(VARCHAR(50),@PAGEINDEX * @PAGESIZE)
      SET @SQL =  ' SELECT '+'*'+ 'FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@SORDER+') AS ROWNUM,'+'*'
      + 'FROM '+@TBLNAME+') AS D
         WHERE ROWNUM BETWEEN '+@START_ID+' AND '
            +@END_ID +' AND '+ @STRWHERE
                  +' ORDER BY '+@SORDER
END
END

SET @SQL=' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@SORDER+') AS ROWNUM,*'
SET @SQL=@SQL+ ' FROM( SELECT TOP(100) PERCENT * FROM '+@TBLNAME
      +' WHERE '+@STRWHERE+' ORDER BY '+@SORDER+')TM1)TM '
SET @SQL=@SQL+' WHERE ROWNUM BETWEEN '+@START_ID+' AND ' +@END_ID

EXEC(@SQL)

作者: erp8@live.cn 时间: 2013-11-19 09:43
本帖最后由 erp8@live.cn 于 2013-11-19 09:47 编辑

谢谢分享
请问一下:GETRECORDFROMPAGE
这个存储过程是怎么在DEMO.cs 中调用的啊?

欢迎光临 FineUI 官方论坛 (https://fineui.com/bbs/)