四个通用分页存储过程(来源于www.codeproject.com)

  1--SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U'
  2----显示数据库的所有表的属性值
  3use bajsyy
  4GO
  5
  6select
  7     ( case when a.colorder = 1 then d.name else '' end ) 表名,
  8     a.colorder 字段序号,
  9     a.name 字段名,
 10     ( case when COLUMNPROPERTY (a.id,a.name,'isidentity'= 1 then '' else '' end ) 标识,
 11     ( case when (
 12            select count(*from sysobjects
 13                where name in (
 14                    select name from sysindexes
 15                    where (id = a.id ) and ( indid in
 16                        (select indid from sysindexkeys where
 17                            ( id = a.id ) and ( colid in (
 18                                select colid from syscolumns
 19                                    where ( id = a.id ) and ( name = a.name ))))))
 20                    and ( xtype ='PK')) > 0 then '' else '' end ) 主键,
 21    b.name 类型,
 22    a.length 字节数,
 23    COLUMNPROPERTY ( a.id,a.name ,'PRECISION' ) as 长度,
 24    isnull ( COLUMNPROPERTY ( a.id,a.name ,'Scale'),0as 小数位数,
 25    (case when a.isnullable = 1 then '' else '' end ) 允许空,
 26    isnull ( e.text,'') 默认值,
 27    isnull (g.[value],'' ) as 字段说明
 28from syscolumns a left join systypes b
 29on a.xtype = b.xusertype
 30inner join sysobjects d
 31on a.id = d.id and d.xtype='U' and d.name <> 'dtproperties'
 32left join syscomments e
 33on a.cdefault = e.id
 34left join sysproperties g
 35on a.id = g.id and a.colid = g.smallid
 36order by a.id ,a.colorder
 37
 38
 39----------分页代码测试
 40/*
 41----------------------------------------------------
 42        spDataPaging 1.0.0.3
 43----------------------------------------------------
 44Autor:    Luis Ruiz Arauz        Date:     2005/06/12        
 45----------------------------------------------------
 46Makes a paged query with the sql parameters to build it
 47and page parameters to filter.
 48Add "No","CurrentPage","TotalPages" coumns to the query
 49to manage paging at user interface.
 50The query Lose the identity column order
 51It works fine for me!
 52----------------------------------------------------
 53Samples:
 54EXEC spDataPaging 'VistaOrders','OrderID,CustomerID','OrderID > 10258','','',15,30
 55EXEC spDataPaging 'VistaOrders','COUNT(OrderID)OrdersCount ,CustomerId','','CustomerID','CustomerID',2,30
 56EXEC spDataPaging 'Customers','*','','','',2,30
 57
 58EXEC spDataPaging 'BirthControl','*','',2,30
 59----------------------------------------------------
 60BphyID CardDate Be_Scrutator  View_BirthControlList
 61EXEC spDataPaging 'View_BirthControlList','*','',1,20
 62*/

 63IF EXISTS (SELECT name FROM sysobjects
 64         WHERE name = 'spDataPaging' AND type = 'P')
 65 DROP PROCEDURE spDataPaging
 66GO
 67CREATE PROC spDataPaging
 68    @TableOrView    VARCHAR(128),
 69    @Columns    VARCHAR(1000= '*',
 70    @Criteria    VARCHAR(3000= '',
 71    @Page        INTEGER = 1,
 72    @PageSize    INTEGER = 30
 73    
 74AS
 75DECLARE @TableTemp    VARCHAR(100),
 76    @IdentityName      VARCHAR(50),
 77    @IdentityType      VARCHAR(20),
 78    @SQLScript    VARCHAR(5000)
 79
 80SET NOCOUNT ON
 81
 82SET @Columns = REPLACE(@Columns,' ','')
 83
 84SET  @TableTemp = @TableOrView
 85
 86IF(LEN(@TableTemp!= LEN(REPLACE(@TableTemp,'..','')))
 87BEGIN
 88    SET @TableTemp =  RIGHT(@TableTemp,LEN(@TableTemp- CHARINDEX('..',@TableTemp- 1)
 89END
 90
 91SELECT @IdentityName = b.name,@IdentityType = c.DATA_TYPE
 92FROM sysobjects a, syscolumns b,Information_Schema.COLUMNS c
 93WHERE a.id = b.id
 94AND a.name = @TableTemp
 95AND c.TABLE_NAME = a.name
 96AND c.COLUMN_NAME = b.name
 97AND c.TABLE_CATALOG = DB_NAME()
 98AND b.status = 128
 99
100SET  @TableTemp = '#' + @TableTemp
101
102IF     @IdentityName IS NOT NULL
103    AND ((LEN(@Columns!= LEN(REPLACE(@Columns,@IdentityName,'')))OR @Columns = '*')
104    AND ((LEN(@Columns= LEN(REPLACE(@Columns,'(' + @IdentityName + ')',''))) OR @Columns = '*')
105 BEGIN
106    SELECT @SQLScript =     ' SELECT ' + @Columns  + ',CAST('  + @IdentityName +  ' AS ' + @IdentityType + ') Num INTO ' + @TableTemp + ' FROM ' + @TableOrView + ' ' + @Criteria +
107                ' ALTER TABLE ' + @TableTemp + ' DROP COLUMN '  + @IdentityName +
108                ' ALTER TABLE ' + @TableTemp + ' ADD No ' + @IdentityType + ' IDENTITY' +
109                ' SELECT * INTO ' + @TableTemp +  '2 FROM ' + @TableTemp +
110                ' SELECT *,CAST(Num AS ' + @IdentityType + '' + @IdentityName + ' INTO ' + @TableTemp + '3 FROM ' + @TableTemp + '2 WHERE (No BETWEEN ' + CAST((@Page * @PageSize - @PageSize + 1AS VARCHAR(20)) + ' AND ' + CAST((@Page * @PageSizeAS VARCHAR(20)) +
111
112
113
114
115 ')' +
116                ' ALTER TABLE ' + @TableTemp + '3 DROP COLUMN Num' +
117                ' SELECT *,' + CAST(@Page AS VARCHAR(20)) + ' CurrentPage,CEILING(((SELECT COUNT(*) FROM ' + @TableTemp + '2) - 1)/' + CAST(@PageSize AS VARCHAR(20)) + ') + 1 TotalPages FROM ' +  @TableTemp + '3'
118 END
119ELSE
120 BEGIN
121    SELECT @SQLScript =     ' SELECT ' + @Columns  + ' INTO ' + @TableTemp + ' FROM ' + @TableOrView + ' ' + @Criteria +
122                ' ALTER TABLE ' + @TableTemp + ' ADD No INT IDENTITY' +
123                ' SELECT * INTO ' + @TableTemp +  '2 FROM ' + @TableTemp +
124                ' SELECT * INTO ' + @TableTemp + '3 FROM ' + @TableTemp + '2 WHERE (No BETWEEN ' + CAST((@Page * @PageSize - @PageSize + 1AS VARCHAR(20)) + ' AND ' + CAST((@Page * @PageSizeAS VARCHAR(20)) + ')' +
125                ' SELECT *,(SELECT COUNT(*) FROM ' + @TableTemp + '2) TotalRows,' + CAST(@Page AS VARCHAR(20)) + ' CurrentPage,CEILING(((SELECT COUNT(*) FROM ' + @TableTemp + '2) - 1)/' + CAST(@PageSize AS VARCHAR(20)) + ') + 1 TotalPages FROM ' +  @TableTemp + '3'
126 END
127
128--SELECT @SQLScript
129
130EXEC(@SQLScript)
131
132
133
134----2-------------------------------------------分页SQL代码2
135IF EXISTS (SELECT name FROM sysobjects
136         WHERE name = 'SP_Pagination' AND type = 'P')
137 DROP PROCEDURE SP_Pagination
138GO
139CREATE  PROCEDURE SP_Pagination
140/**//*
141***************************************************************
142** 千万数量级分页存储过程 **
143***************************************************************
144参数说明:
1451.Tables :表名称,视图
1462.PrimaryKey :主关键字
1473.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
1484.CurrentPage :当前页码
1495.PageSize :分页尺寸
1506.Filter :过滤语句,不带Where
1517.Group :Group语句,不带Group By
152View_BirthControlList
153
154EXEC SP_Pagination 'View_BirthControlList','证明号','',1,10,'*','',''
155***************************************************************/

156(
157@Tables varchar(2000),
158@PrimaryKey varchar(500),
159@Sort varchar(500= NULL,
160@CurrentPage int = 1,
161@PageSize int = 10,
162@Fields varchar(2000= '*',
163@Filter varchar(1000= NULL,
164@Group varchar(1000= NULL
165)
166AS
167/**//*默认排序*/
168IF @Sort IS NULL OR @Sort = ''
169SET @Sort = @PrimaryKey
170DECLARE @SortTable varchar(1000)
171DECLARE @SortName varchar(1000)
172DECLARE @strSortColumn varchar(1000)
173DECLARE @operator char(2)
174DECLARE @type varchar(1000)
175DECLARE @prec int
176/**//*设定排序语句.*/
177IF CHARINDEX('DESC',@Sort)>0
178BEGIN
179SET @strSortColumn = REPLACE(@Sort'DESC''')
180SET @operator = '<='
181END
182ELSE
183BEGIN
184IF CHARINDEX('ASC'@Sort= 0
185SET @strSortColumn = REPLACE(@Sort'ASC''')
186SET @operator = '>='
187END
188IF CHARINDEX('.'@strSortColumn> 0
189BEGIN
190SET @SortTable = SUBSTRING(@strSortColumn0CHARINDEX('.',@strSortColumn))
191SET @SortName = SUBSTRING(@strSortColumnCHARINDEX('.',@strSortColumn+ 1LEN(@strSortColumn))
192END
193ELSE
194BEGIN
195SET @SortTable = @Tables
196SET @SortName = @strSortColumn
197END
198SELECT @type=t.name, @prec=c.prec
199FROM sysobjects o
200JOIN syscolumns c on o.id=c.id
201JOIN systypes t on c.xusertype=t.xusertype
202WHERE o.name = @SortTable AND c.name = @SortName
203IF CHARINDEX('char'@type> 0
204SET @type = @type + '(' + CAST(@prec AS varchar+ ')'
205DECLARE @strPageSize varchar(500)
206DECLARE @strStartRow varchar(500)
207DECLARE @strFilter varchar(1000)
208DECLARE @strSimpleFilter varchar(1000)
209DECLARE @strGroup varchar(1000)
210/**//*默认当前页*/
211IF @CurrentPage < 1
212SET @CurrentPage = 1
213/**//*设置分页参数.*/
214SET @strPageSize = CAST(@PageSize AS varchar(500))
215SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1AS varchar(500))
216/**//*筛选以及分组语句.*/
217IF @Filter IS NOT NULL AND @Filter != ''
218BEGIN
219SET @strFilter = ' WHERE ' + @Filter + ' '
220SET @strSimpleFilter = ' AND ' + @Filter + ' '
221END
222ELSE
223BEGIN
224SET @strSimpleFilter = ''
225SET @strFilter = ''
226END
227IF @Group IS NOT NULL AND @Group != ''
228SET @strGroup = ' GROUP BY ' + @Group + ' '
229ELSE
230SET @strGroup = ''
231/**//*执行查询语句*/
232EXEC(
233'
234DECLARE @SortColumn ' + @type + '
235SET ROWCOUNT ' + @strStartRow + '
236SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
237SET ROWCOUNT ' + @strPageSize + '
238SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
239'
240)
241
242
243
244-------------------------3分页
245
246CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
247    @orderedOnStart datetime,
248    @orderedOnEnd datetime,
249    @pageNumber int,
250    @pageSize int,
251    @sortExpression varchar(100),
252    @sortOrder varchar(4),
253    @virtualCount int OUTPUT
254AS
255
256    /*
257        Make sure that the page number is at least 1
258    */

259    IF @pageNumber < 1
260    BEGIN
261        SET @pageNumber = 1
262    END
263
264    SELECT
265        @virtualCount = COUNT(*)
266    FROM
267        Sales.SalesOrderHeader Header
268    WHERE
269        Header.[OrderDate] >= @orderedOnStart
270        AND Header.[OrderDate] < @orderedOnEnd
271
272    DECLARE @lastKeyValue numeric(18,0)
273    DECLARE @lastAscendingSortValue SQL_Variant
274    DECLARE @lastDescendingSortValue SQL_Variant
275
276    DECLARE @numberToIgnore int
277
278    SET @numberToIgnore = (@pageNumber-1* @pageSize
279
280    IF @numberToIgnore > 0
281    BEGIN
282        /*
283        Get the last available sort data and unique key
284        value from the last page.
285        */

286        SET ROWCOUNT @numberToIgnore
287
288        SELECT
289            @lastKeyValue = [UniqueValue],
290            @lastAscendingSortValue = [AscendingSort],
291            @lastDescendingSortValue = [DescendingSort]
292        FROM
293        (
294            SELECT
295                Header.[SalesOrderID] AS [UniqueValue],
296                CASE
297                    WHEN
298                        UPPER(@sortOrder= 'DESC'
299                        AND UPPER(@sortExpression= 'CUSTOMERID'
300                    THEN
301                        CONVERT(SQL_Variant, [CustomerID])
302                    WHEN
303                        UPPER(@sortOrder= 'DESC'
304                        AND UPPER(@sortExpression= 'TOTALDUE'
305                    THEN
306                        CONVERT(SQL_Variant, [TotalDue])
307                    WHEN
308                        UPPER(@sortOrder= 'DESC'
309                        AND UPPER(@sortExpression= 'ORDERDATE'
310                    THEN
311                        CONVERT(SQL_Variant, [OrderDate])
312                    ELSE
313                        NULL
314                END AS [DescendingSort],
315                CASE
316                    WHEN
317                        UPPER(@sortOrder= 'ASC'
318                        AND UPPER(@sortExpression= 'CUSTOMERID'
319                    THEN
320                        CONVERT(SQL_Variant, [CustomerID])
321                    WHEN
322                        UPPER(@sortOrder= 'ASC'
323                        AND UPPER(@sortExpression= 'TOTALDUE'
324                    THEN
325                        CONVERT(SQL_Variant, [TotalDue])
326                    WHEN
327                        UPPER(@sortOrder= 'ASC'
328                        AND UPPER(@sortExpression= 'ORDERDATE'
329                    THEN
330                        CONVERT(SQL_Variant, [OrderDate])
331                    ELSE
332                        NULL
333                END AS [AscendingSort]
334            FROM
335                Sales.SalesOrderHeader Header
336            WHERE
337                Header.[OrderDate] >= @orderedOnStart
338                AND Header.[OrderDate] < @orderedOnEnd
339            ) AS Derived
340        ORDER BY
341            [AscendingSort] ASC,
342            [DescendingSort] DESC,
343            [UniqueValue] ASC
344    END
345
346    /*
347    Select the first @pageSize records that come after the last sort
348    data/unique value from the last page. If this is the first page,
349    just get the first @pageSize records.
350    */

351
352    SET ROWCOUNT @pageSize
353
354    SELECT
355        [SalesOrderID],
356        [OrderDate],
357        [TotalDue],
358        [CustomerID]
359    FROM
360    (
361        SELECT
362            [SalesOrderID],
363            [OrderDate],
364            [TotalDue],
365            [CustomerID],
366            [SalesOrderID] As [UniqueValue],
367            CASE
368                WHEN
369                    UPPER(@sortOrder= 'DESC'
370                    AND UPPER(@sortExpression= 'CUSTOMERID'
371                THEN
372                    CONVERT(SQL_Variant, [CustomerID])
373                WHEN
374                    UPPER(@sortOrder= 'DESC'
375                    AND UPPER(@sortExpression= 'TOTALDUE'
376                THEN
377                    CONVERT(SQL_Variant, [TotalDue])
378                WHEN
379                    UPPER(@sortOrder= 'DESC'
380                    AND UPPER(@sortExpression= 'ORDERDATE'
381                THEN
382                    CONVERT(SQL_Variant, [OrderDate])
383                ELSE
384                    NULL
385            END AS [DescendingSort],
386            CASE
387                WHEN
388                    UPPER(@sortOrder= 'ASC'
389                    AND UPPER(@sortExpression= 'CUSTOMERID'
390                THEN
391                    CONVERT(SQL_Variant, [CustomerID])
392                WHEN
393                    UPPER(@sortOrder= 'ASC'
394                    AND UPPER(@sortExpression= 'TOTALDUE'
395                THEN
396                    CONVERT(SQL_Variant, [TotalDue])
397                WHEN
398                    UPPER(@sortOrder= 'ASC'
399                    AND UPPER(@sortExpression= 'ORDERDATE'
400                THEN
401                    CONVERT(SQL_Variant, [OrderDate])
402                ELSE
403                    NULL
404            END AS [AscendingSort]
405        FROM
406            Sales.SalesOrderHeader Header
407        WHERE
408            Header.[OrderDate] >= @orderedOnStart
409            AND Header.[OrderDate] < @orderedOnEnd
410    ) Derived
411    WHERE
412    (
413        @lastAscendingSortValue IS NULL
414        AND @lastDescendingSortValue IS NULL
415        AND @lastKeyValue IS NULL
416    )
417    OR
418    (
419        (@lastAscendingSortValue IS NOT NULL)
420        AND
421        (
422            ([AscendingSort] > @lastAscendingSortValue)
423            OR
424            (
425                [AscendingSort] = @lastAscendingSortValue
426                AND [UniqueValue] > @lastKeyValue
427            )
428        )
429    )
430    OR
431    (
432        (@lastDescendingSortValue IS NOT NULL)
433        AND
434        (
435            ([DescendingSort] < @lastDescendingSortValue)
436            OR
437            (
438                [DescendingSort] = @lastDescendingSortValue
439                AND [UniqueValue] > @lastKeyValue
440            )
441        )
442    )
443    ORDER BY
444        [AscendingSort] ASC,
445        [DescendingSort] DESC,
446        [SalesOrderID] ASC
447
448    SET ROWCOUNT 0
449GO
450
451
452-------------4分页
453
454SET QUOTED_IDENTIFIER ON
455GO
456SET ANSI_NULLS ON
457GO
458
459    ALTER  PROCEDURE sp_Easypaging
460
461    (
462   
463    @Tables varchar(1000),
464    @PK varchar(100),
465    @JoinStatements varchar(1000)='',
466    @Fields varchar(5000= '*',
467    @Filter varchar(5000= NULL,
468    @Sort varchar(200= NULL,
469    @PageNumber int = 1,
470    @PageSize int = 10,
471    @TotalRec int =0 Output,
472    @Group varchar(1000= NULL
473   
474       
475    )
476
477AS
478
479
480
481/*
482Created by Kashif Akram
483Email Muhammad_kashif@msn.com
484
485The publication rights are reserved 
486You can use this procedure with out removing these comments
487*/

488
489
490
491DECLARE @strPageSize varchar(50)
492DECLARE @strStartRow varchar(50)
493
494
495SET @strPageSize = CAST(@PageSize AS varchar(50))
496SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1AS varchar(50))
497
498
499
500--set @PK =' tbl_Items.ItemID '
501
502create table #PageTable (PID  bigint primary key IDENTITY (11) , UID int)
503create table #PageIndex (UID int)
504
505/*
506CREATE UNIQUE CLUSTERED
507  INDEX [PK_tbl_PageTable] ON #PageTable (PID)
508*/

509CREATE 
510  INDEX [PK_tbl_PageIndex] ON #PageIndex (UID)
511
512
513--'SELECT ' + @Fields + ' FROM ' + @Tables + '' + @JoinStatements +' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort +  ' DESC '
514exec ('
515
516set rowcount 0
517
518    insert into #pageTable(UID)
519     SELECT ' + @PK + ' FROM ' + @Tables + ' ' + @JoinStatements +' WHERE ' +  @Filter + ' ' + @Group + ' ORDER BY ' + @Sort +  '
520
521
522DECLARE @SortColumn int
523
524SET ROWCOUNT '+  @strStartRow +'
525
526select  @SortColumn=PID from #PageTable --option (keep plan)
527
528print @SortColumn
529
530SET ROWCOUNT '+  @strPageSize +'
531
532insert into #pageIndex
533select UID from #PageTable where PID >= @SortColumn -- option (keep plan)
534
535
536         
537SELECT ' + @Fields + ' FROM ' + @Tables + ' ' + @JoinStatements +' WHERE ' +  @Filter + ' and  '+ @PK + ' in (Select UID from #pageIndex)' + @Group + ' ORDER BY ' + @Sort +  ' '
538
539 )
540
541
542
543select @TotalRec=count(*from  #pageTable
544
545
546
547
548
549drop table #PageTable
550drop table #PageIndex
551
552
553   
554RETURN
555
556GO
557SET QUOTED_IDENTIFIER OFF
558GO
559SET ANSI_NULLS ON
560GO
posted @ 2009-01-10 00:17  ®Geovin Du Dream Park™  阅读(527)  评论(0编辑  收藏  举报