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

1,场景:根据学生编号查询,返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。
复制代码 代码如下: CREATE PROC
[dbo].[Sp_testpagerandsorting] (@GroupID INT, @CurrentId INT,
@TimeFrom DATETIME, @TimeTo DATETIME, @OrderBy CHAR(50), @PageSize INT,
@CurrentPage INT) AS SET nocount ON BEGIN DECLARE @StartNumber INT,
@EndNumber INT, @CurrentIdRowNumber INT, @RecordCount INT, @EndPageIndex
INT DECLARE @RowNumberTable TABLE ( rownumber INT IDENTITY (1, 1), id
INT ) –step 1: Build sort id list
——————————————————- INSERT INTO
@RowNumberTable (id) SELECT sm.id AS id FROM dbo.test sm WITH (nolock)
WHERE indate BETWEEN Coalesce(@TimeFrom, indate) AND Coalesce(@TimeTo,
indate) AND sm.groupid = @GroupID ORDER BY CASE WHEN @OrderBy = ‘InDate
desc’ THEN ( Row_number() OVER (ORDER BY indate DESC)) WHEN @OrderBy =
‘InDate asc’ THEN (Row_number() OVER (ORDER BY indate ASC)) WHEN
@OrderBy = ‘Id asc’ THEN (Row_number() OVER (ORDER BY sm.id ASC)) WHEN
@OrderBy = ‘Id desc’ THEN (Row_number() OVER (ORDER BY sm.id DESC))
WHEN @OrderBy = ‘Name asc’ THEN (Row_number() OVER (ORDER BY sm.name
ASC)) WHEN @OrderBy = ‘Name desc’ THEN (Row_number() OVER (ORDER BY
sm.name DESC) ) END –step 2: Reset page index with current id
—————————————– IF @CurrentIdNumber 0 BEGIN
SELECT TOP 1 @CurrentIdRowNumber = rownumber FROM @RowNumberTable WHERE
id = @CurrentIdNumber IF @CurrentIdRowNumber 0 BEGIN IF @CurrentPage = 0
BEGIN SET @CurrentPage = Ceiling(CAST(@CurrentIdRowNumber AS DECIMAL) /
CAST (@PageSize AS DECIMAL)) END END END ELSE BEGIN IF @CurrentPage = 0
BEGIN SET @CurrentPage = 1 END END –step 3: Set recordCount
—————————————– SELECT @RecordCount = COUNT(1)
FROM @RowNumberTable –step 4: Calc startNumber & endNumber
—————————————– SELECT @StartNumber =
@PageSize * ( @CurrentPage – 1 ), @EndNumber = @PageSize * (
@CurrentPage – 1 ) + @pageSize, @EndPageIndex =
Ceiling(CAST(@RecordCount AS DECIMAL) / CAST(@PageSize AS DECIMAL)) IF
@CurrentPage = @EndPageIndex BEGIN SET @EndNumber = @RecordCount END
–step 5: Get sorted id of current page
—————————————– ;WITH a AS (SELECT TOP
(@EndNumber – @StartNumber) id, rownumber FROM (SELECT TOP (@EndNumber)
id, rownumber FROM @RowNumberTable) AS b ORDER BY rownumber DESC) –step
6: Return current page idList
——————————————————- SELECT [ID],
[GroupID] [Name], [Address] FROM dbo.test sm WITH(nolock) INNER
JOIN a ON a.id = sm.id ORDER BY a.rownumber — step 7:return current
page & record count ———————————- SELECT
@CurrentPage AS currentpage, @RecordCount AS recordcount END
2,简单条件的,动态where语句(关于Like查询的动态where,建议使用笨办法做)
复制代码 代码如下: CREATE PROC
[dbo].[Getstudentlistbycondition] @Name NVARCHAR(20), @Class INT AS
SET nocount ON BEGIN BEGIN SELECT [Name], [class] FROM [testtable]
WHERE [Class] = CASE WHEN @Class 0 THEN @Class ELSE [Class] END AND
[name] = CASE WHEN @Name ” THEN @Name ELSE [Name] END END END

  1图片 1–SELECT NAME FROM SYSOBJECTS WHERE TYPE = ‘U’
  2图片 2—-显示数据库的所有表的属性值
  3图片 3use bajsyy
  4图片 4GO
  5图片 5
  6图片 6select
  7图片 7     ( case when a.colorder = 1 then d.name else ” end ) 表名,
  8图片 8     a.colorder 字段序号,
  9图片 9     a.name 字段名,
 10图片 10     ( case when COLUMNPROPERTY (a.id,a.name,’isidentity’) = 1 then ‘√’ else ” end ) 标识,
 11图片 11     ( case when (
 12图片 12            select count(*) from sysobjects
 13图片 13                where name in (
 14图片 14                    select name from sysindexes
 15图片 15                    where (id = a.id ) and ( indid in
 16图片 16                        (select indid from sysindexkeys where
 17图片 17                            ( id = a.id ) and ( colid in (
 18图片 18                                select colid from syscolumns
 19图片 19                                    where ( id = a.id ) and ( name = a.name ))))))
 20图片 20                    and ( xtype =’PK’)) > 0 then ‘√’ else ” end ) 主键,
 21图片 21    b.name 类型,
 22图片 22    a.length 字节数,
 23图片 23    COLUMNPROPERTY ( a.id,a.name ,’PRECISION’ ) as 长度,
 24图片 24    isnull ( COLUMNPROPERTY ( a.id,a.name ,’Scale’),0) as 小数位数,
 25图片 25    (case when a.isnullable = 1 then ‘√’ else ” end ) 允许空,
 26图片 26    isnull ( e.text,”) 默认值,
 27图片 27    isnull (g.[value],” ) as 字段说明
 28图片 28from syscolumns a left join systypes b
 29图片 29on a.xtype = b.xusertype
 30图片 30inner join sysobjects d
 31图片 31on a.id = d.id and d.xtype=’U’ and d.name <> ‘dtproperties’
 32图片 32left join syscomments e
 33图片 33on a.cdefault = e.id
 34图片 34left join sysproperties g
 35图片 35on a.id = g.id and a.colid = g.smallid
 36图片 36order by a.id ,a.colorder
 37图片 37
 38图片 38
 39图片 39———-分页代码测试
 40图片 40图片 41/**//*
 41图片 42—————————————————-
 42图片 43        spDataPaging 1.0.0.3
 43图片 44—————————————————-
 44图片 45Autor:    Luis Ruiz Arauz        Date:     2005/06/12        
 45图片 46—————————————————-
 46图片 47Makes a paged query with the sql parameters to build it
 47图片 48and page parameters to filter.
 48图片 49Add “No”,”CurrentPage”,”TotalPages” coumns to the query
 49图片 50to manage paging at user interface.
 50图片 51The query Lose the identity column order
 51图片 52It works fine for me!
 52图片 53—————————————————-
 53图片 54Samples:
 54图片 55EXEC spDataPaging ‘VistaOrders’,’OrderID,CustomerID’,’OrderID > 10258′,”,”,15,30
 55图片 56EXEC spDataPaging ‘VistaOrders’,’COUNT(OrderID)OrdersCount ,CustomerId’,”,’CustomerID’,’CustomerID’,2,30
 56图片 57EXEC spDataPaging ‘Customers’,’*’,”,”,”,2,30
 57图片 58
 58图片 59EXEC spDataPaging ‘BirthControl’,’*’,”,2,30
 59图片 60—————————————————-
 60图片 61BphyID CardDate Be_Scrutator  View_BirthControlList
 61图片 62EXEC spDataPaging ‘View_BirthControlList’,’*’,”,1,20
 62图片 63*/
 63图片 64IF EXISTS (SELECT name FROM sysobjects
 64图片 65         WHERE name = ‘spDataPaging’ AND type = ‘P’)
 65图片 66 DROP PROCEDURE spDataPaging
 66图片 67GO
 67图片 68CREATE PROC spDataPaging
 68图片 69    @TableOrView    VARCHAR(128),
 69图片 70    @Columns    VARCHAR(1000) = ‘*’,
 70图片 71    @Criteria    VARCHAR(3000) = ”,
 71图片 72    @Page        INTEGER = 1,
 72图片 73    @PageSize    INTEGER = 30
 73图片 74    
 74图片 75AS
 75图片 76DECLARE @TableTemp    VARCHAR(100),
 76图片 77    @IdentityName      VARCHAR(50),
 77图片 78    @IdentityType      VARCHAR(20),
 78图片 79    @SQLScript    VARCHAR(5000)
 79图片 80
 80图片 81SET NOCOUNT ON
 81图片 82
 82图片 83SET @Columns = REPLACE(@Columns,’ ‘,”)
 83图片 84
 84图片 85SET  @TableTemp = @TableOrView
 85图片 86
 86图片 87IF(LEN(@TableTemp) != LEN(REPLACE(@TableTemp,’..’,”)))
 87图片 88BEGIN
 88图片 89    SET @TableTemp =  RIGHT(@TableTemp,LEN(@TableTemp) – CHARINDEX(‘..’,@TableTemp) – 1)
 89图片 90END
 90图片 91
 91图片 92SELECT @IdentityName = b.name,@IdentityType = c.DATA_TYPE
 92图片 93FROM sysobjects a, syscolumns b,Information_Schema.COLUMNS c
 93图片 94WHERE a.id = b.id
 94图片 95AND a.name = @TableTemp
 95图片 96AND c.TABLE_NAME = a.name
 96图片 97AND c.COLUMN_NAME = b.name
 97图片 98AND c.TABLE_CATALOG = DB_NAME()
 98图片 99AND b.status = 128
 99图片 100
100图片 101SET  @TableTemp = ‘#’ + @TableTemp
101图片 102
102图片 103IF     @IdentityName IS NOT NULL
103图片 104    AND ((LEN(@Columns) != LEN(REPLACE(@Columns,@IdentityName,”)))OR @Columns = ‘*’)
104图片 105    AND ((LEN(@Columns) = LEN(REPLACE(@Columns,'(‘ + @IdentityName + ‘)’,”))) OR @Columns = ‘*’)
105图片 106 BEGIN
106图片 107    SELECT @SQLScript =     ‘ SELECT ‘ + @Columns  + ‘,CAST(‘  + @IdentityName +  ‘ AS ‘ + @IdentityType + ‘) Num INTO ‘ + @TableTemp + ‘ FROM ‘ + @TableOrView + ‘ ‘ + @Criteria +
107图片 108                ‘ ALTER TABLE ‘ + @TableTemp + ‘ DROP COLUMN ‘  + @IdentityName +
108图片 109                ‘ ALTER TABLE ‘ + @TableTemp + ‘ ADD No ‘ + @IdentityType + ‘ IDENTITY’ +
109图片 110                ‘ SELECT * INTO ‘ + @TableTemp +  ‘2 FROM ‘ + @TableTemp +
110图片 111                ‘ SELECT *,CAST(Num AS ‘ + @IdentityType + ‘) ‘ + @IdentityName + ‘ INTO ‘ + @TableTemp + ‘3 FROM ‘ + @TableTemp + ‘2 WHERE (No BETWEEN ‘ + CAST((@Page * @PageSize – @PageSize + 1) AS VARCHAR(20)) + ‘ AND ‘ + CAST((@Page * @PageSize) AS VARCHAR(20)) +
111图片 112
112图片 113
113图片 114
114图片 115
115图片 116 ‘)’ +
116图片 117                ‘ ALTER TABLE ‘ + @TableTemp + ‘3 DROP COLUMN Num’ +
117图片 118                ‘ 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图片 119 END
119图片 120ELSE
120图片 121 BEGIN
121图片 122    SELECT @SQLScript =     ‘ SELECT ‘ + @Columns  + ‘ INTO ‘ + @TableTemp + ‘ FROM ‘ + @TableOrView + ‘ ‘ + @Criteria +
122图片 123                ‘ ALTER TABLE ‘ + @TableTemp + ‘ ADD No INT IDENTITY’ +
123图片 124                ‘ SELECT * INTO ‘ + @TableTemp +  ‘2 FROM ‘ + @TableTemp +
124图片 125                ‘ SELECT * INTO ‘ + @TableTemp + ‘3 FROM ‘ + @TableTemp + ‘2 WHERE (No BETWEEN ‘ + CAST((@Page * @PageSize – @PageSize + 1) AS VARCHAR(20)) + ‘ AND ‘ + CAST((@Page * @PageSize) AS VARCHAR(20)) + ‘)’ +
125图片 126                ‘ 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图片 127 END
127图片 128
128图片 129–SELECT @SQLScript
129图片 130
130图片 131EXEC(@SQLScript)
131图片 132
132图片 133
133图片 134
134图片 135—-2——————————————-分页SQL代码2
135图片 136IF EXISTS (SELECT name FROM sysobjects
136图片 137         WHERE name = ‘SP_Pagination’ AND type = ‘P’)
137图片 138 DROP PROCEDURE SP_Pagination
138图片 139GO
139图片 140CREATE  PROCEDURE SP_Pagination
140图片 141图片 142/**//**//**//*
141图片 143***************************************************************
142图片 144** 千万数量级分页存储过程 **
143图片 145***************************************************************
144图片 146参数说明:
145图片 1471.Tables :表名称,视图
146图片 1482.PrimaryKey :主关键字
147图片 1493.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
148图片 1504.CurrentPage :当前页码
149图片 1515.PageSize :分页尺寸
150图片 1526.Filter :过滤语句,不带Where
151图片 1537.Group :Group语句,不带Group By
152图片 154View_BirthControlList
153图片 155
154图片 156EXEC SP_Pagination ‘View_BirthControlList’,’证明号’,”,1,10,’*’,”,”
155图片 157***************************************************************/
156图片 158(
157图片 159@Tables varchar(2000),
158图片 160@PrimaryKey varchar(500),
159图片 161@Sort varchar(500) = NULL,
160图片 162@CurrentPage int = 1,
161图片 163@PageSize int = 10,
162图片 164@Fields varchar(2000) = ‘*’,
163图片 165@Filter varchar(1000) = NULL,
164图片 166@Group varchar(1000) = NULL
165图片 167)
166图片 168AS
167图片 169图片 170/**//**//**//*默认排序*/
168图片 171IF @Sort IS NULL OR @Sort = ”
169图片 172SET @Sort = @PrimaryKey
170图片 173DECLARE @SortTable varchar(1000)
171图片 174DECLARE @SortName varchar(1000)
172图片 175DECLARE @strSortColumn varchar(1000)
173图片 176DECLARE @operator char(2)
174图片 177DECLARE @type varchar(1000)
175图片 178DECLARE @prec int
176图片 179图片 180/**//**//**//*设定排序语句.*/
177图片 181IF CHARINDEX(‘DESC’,@Sort)>0
178图片 182BEGIN
179图片 183SET @strSortColumn = REPLACE(@Sort, ‘DESC’, ”)
180图片 184SET @operator = ‘<=’
181图片 185END
182图片 186ELSE
183图片 187BEGIN
184图片 188IF CHARINDEX(‘ASC’, @Sort) = 0
185图片 189SET @strSortColumn = REPLACE(@Sort, ‘ASC’, ”)
186图片 190SET @operator = ‘>=’
187图片 191END
188图片 192IF CHARINDEX(‘.’, @strSortColumn) > 0
189图片 193BEGIN
190图片 194SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX(‘.’,@strSortColumn))
191图片 195SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX(‘.’,@strSortColumn) + 1, LEN(@strSortColumn))
192图片 196END
193图片 197ELSE
194图片 198BEGIN
195图片 199SET @SortTable = @Tables
196图片 200SET @SortName = @strSortColumn
197图片 201END
198图片 202SELECT @type=t.name, @prec=c.prec
199图片 203FROM sysobjects o
200图片 204JOIN syscolumns c on o.id=c.id
201图片 205JOIN systypes t on c.xusertype=t.xusertype
202图片 206WHERE o.name = @SortTable AND c.name = @SortName
203图片 207IF CHARINDEX(‘char’, @type) > 0
204图片 208SET @type = @type + ‘(‘ + CAST(@prec AS varchar) + ‘)’
205图片 209DECLARE @strPageSize varchar(500)
206图片 210DECLARE @strStartRow varchar(500)
207图片 211DECLARE @strFilter varchar(1000)
208图片 212DECLARE @strSimpleFilter varchar(1000)
209图片 213DECLARE @strGroup varchar(1000)
210图片 214图片 215/**//**//**//*默认当前页*/
211图片 216IF @CurrentPage < 1
212图片 217SET @CurrentPage = 1
213图片 218图片 219/**//**//**//*设置分页参数.*/
214图片 220SET @strPageSize = CAST(@PageSize AS varchar(500))
215图片 221SET @strStartRow = CAST(((@CurrentPage – 1)*@PageSize + 1) AS varchar(500))
216图片 222图片 223/**//**//**//*筛选以及分组语句.*/
217图片 224IF @Filter IS NOT NULL AND @Filter != ”
218图片 225BEGIN
219图片 226SET @strFilter = ‘ WHERE ‘ + @Filter + ‘ ‘
220图片 227SET @strSimpleFilter = ‘ AND ‘ + @Filter + ‘ ‘
221图片 228END
222图片 229ELSE
223图片 230BEGIN
224图片 231SET @strSimpleFilter = ”
225图片 232SET @strFilter = ”
226图片 233END
227图片 234IF @Group IS NOT NULL AND @Group != ”
228图片 235SET @strGroup = ‘ GROUP BY ‘ + @Group + ‘ ‘
229图片 236ELSE
230图片 237SET @strGroup = ”
231图片 238图片 239/**//**//**//*执行查询语句*/
232图片 240EXEC(
233图片 241
234图片 242DECLARE @SortColumn ‘ + @type + ‘
235图片 243SET ROWCOUNT ‘ + @strStartRow + ‘
236图片 244SELECT @SortColumn=’ + @strSortColumn + ‘ FROM ‘ + @Tables + @strFilter + ‘ ‘ + @strGroup + ‘ ORDER BY ‘ + @Sort + ‘
237图片 245SET ROWCOUNT ‘ + @strPageSize + ‘
238图片 246SELECT ‘ + @Fields + ‘ FROM ‘ + @Tables + ‘ WHERE ‘ + @strSortColumn + @operator + ‘ @SortColumn ‘ + @strSimpleFilter + ‘ ‘ + @strGroup + ‘ ORDER BY ‘ + @Sort + ‘
239图片 247
240图片 248)
241图片 249
242图片 250
243图片 251
244图片 252————————-3分页
245图片 253
246图片 254CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
247图片 255    @orderedOnStart datetime,
248图片 256    @orderedOnEnd datetime,
249图片 257    @pageNumber int,
250图片 258    @pageSize int,
251图片 259    @sortExpression varchar(100),
252图片 260    @sortOrder varchar(4),
253图片 261    @virtualCount int OUTPUT
254图片 262AS
255图片 263
256图片 264图片 265    /**//*
257图片 266        Make sure that the page number is at least 1
258图片 267    */
259图片 268    IF @pageNumber < 1
260图片 269    BEGIN
261图片 270        SET @pageNumber = 1
262图片 271    END
263图片 272
264图片 273    SELECT
265图片 274        @virtualCount = COUNT(*)
266图片 275    FROM
267图片 276        Sales.SalesOrderHeader Header
268图片 277    WHERE
269图片 278        Header.[OrderDate] >= @orderedOnStart
270图片 279        AND Header.[OrderDate] < @orderedOnEnd
271图片 280
272图片 281    DECLARE @lastKeyValue numeric(18,0)
273图片 282    DECLARE @lastAscendingSortValue SQL_Variant
274图片 283    DECLARE @lastDescendingSortValue SQL_Variant
275图片 284
276图片 285    DECLARE @numberToIgnore int
277图片 286
278图片 287    SET @numberToIgnore = (@pageNumber-1) * @pageSize
279图片 288
280图片 289    IF @numberToIgnore > 0
281图片 290    BEGIN
282图片 291图片 292        /**//*
283图片 293        Get the last available sort data and unique key
284图片 294        value from the last page.
285图片 295        */
286图片 296        SET ROWCOUNT @numberToIgnore
287图片 297
288图片 298        SELECT
289图片 299            @lastKeyValue = [UniqueValue],
290图片 300            @lastAscendingSortValue = [AscendingSort],
291图片 301            @lastDescendingSortValue = [DescendingSort]
292图片 302        FROM
293图片 303        (
294图片 304            SELECT
295图片 305                Header.[SalesOrderID] AS [UniqueValue],
296图片 306                CASE
297图片 307                    WHEN
298图片 308                        UPPER(@sortOrder) = ‘DESC’
299图片 309                        AND UPPER(@sortExpression) = ‘CUSTOMERID’
300图片 310                    THEN
301图片 311                        CONVERT(SQL_Variant, [CustomerID])
302图片 312                    WHEN
303图片 313                        UPPER(@sortOrder) = ‘DESC’
304图片 314                        AND UPPER(@sortExpression) = ‘TOTALDUE’
305图片 315                    THEN
306图片 316                        CONVERT(SQL_Variant, [TotalDue])
307图片 317                    WHEN
308图片 318                        UPPER(@sortOrder) = ‘DESC’
309图片 319                        AND UPPER(@sortExpression) = ‘ORDERDATE’
310图片 320                    THEN
311图片 321                        CONVERT(SQL_Variant, [OrderDate])
312图片 322                    ELSE
313图片 323                        NULL
314图片 324                END AS [DescendingSort],
315图片 325                CASE
316图片 326                    WHEN
317图片 327                        UPPER(@sortOrder) = ‘ASC’
318图片 328                        AND UPPER(@sortExpression) = ‘CUSTOMERID’
319图片 329                    THEN
320图片 330                        CONVERT(SQL_Variant, [CustomerID])
321图片 331                    WHEN
322图片 332                        UPPER(@sortOrder) = ‘ASC’
323图片 333                        AND UPPER(@sortExpression) = ‘TOTALDUE’
324图片 334                    THEN
325图片 335                        CONVERT(SQL_Variant, [TotalDue])
326图片 336                    WHEN
327图片 337                        UPPER(@sortOrder) = ‘ASC’
328图片 338                        AND UPPER(@sortExpression) = ‘ORDERDATE’
329图片 339                    THEN
330图片 340                        CONVERT(SQL_Variant, [OrderDate])
331图片 341                    ELSE
332图片 342                        NULL
333图片 343                END AS [AscendingSort]
334图片 344            FROM
335图片 345                Sales.SalesOrderHeader Header
336图片 346            WHERE
337图片 347                Header.[OrderDate] >= @orderedOnStart
338图片 348                AND Header.[OrderDate] < @orderedOnEnd
339图片 349            ) AS Derived
340图片 350        ORDER BY
341图片 351            [AscendingSort] ASC,
342图片 352            [DescendingSort] DESC,
343图片 353            [UniqueValue] ASC
344图片 354    END
345图片 355
346图片 356图片 357    /**//*
347图片 358    Select the first @pageSize records that come after the last sort
348图片 359    data/unique value from the last page. If this is the first page,
349图片 360    just get the first @pageSize records.
350图片 361    */
351图片 362
352图片 363    SET ROWCOUNT @pageSize
353图片 364
354图片 365    SELECT
355图片 366        [SalesOrderID],
356图片 367        [OrderDate],
357图片 368        [TotalDue],
358图片 369        [CustomerID]
359图片 370    FROM
360图片 371    (
361图片 372        SELECT
362图片 373            [SalesOrderID],
363图片 374            [OrderDate],
364图片 375            [TotalDue],
365图片 376            [CustomerID],
366图片 377            [SalesOrderID] As [UniqueValue],
367图片 378            CASE
368图片 379                WHEN
369图片 380                    UPPER(@sortOrder) = ‘DESC’
370图片 381                    AND UPPER(@sortExpression) = ‘CUSTOMERID’
371图片 382                THEN
372图片 383                    CONVERT(SQL_Variant, [CustomerID])
373图片 384                WHEN
374图片 385                    UPPER(@sortOrder) = ‘DESC’
375图片 386                    AND UPPER(@sortExpression) = ‘TOTALDUE’
376图片 387                THEN
377图片 388                    CONVERT(SQL_Variant, [TotalDue])
378图片 389                WHEN
379图片 390                    UPPER(@sortOrder) = ‘DESC’
380图片 391                    AND UPPER(@sortExpression) = ‘ORDERDATE’
381图片 392                THEN
382图片 393                    CONVERT(SQL_Variant, [OrderDate])
383图片 394                ELSE
384图片 395                    NULL
385图片 396            END AS [DescendingSort],
386图片 397            CASE
387图片 398                WHEN
388图片 399                    UPPER(@sortOrder) = ‘ASC’
389图片 400                    AND UPPER(@sortExpression) = ‘CUSTOMERID’
390图片 401                THEN
391图片 402                    CONVERT(SQL_Variant, [CustomerID])
392图片 403                WHEN
393图片 404                    UPPER(@sortOrder) = ‘ASC’
394图片 405                    AND UPPER(@sortExpression) = ‘TOTALDUE’
395图片 406                THEN
396图片 407                    CONVERT(SQL_Variant, [TotalDue])
397图片 408                WHEN
398图片 409                    UPPER(@sortOrder) = ‘ASC’
399图片 410                    AND UPPER(@sortExpression) = ‘ORDERDATE’
400图片 411                THEN
401图片 412                    CONVERT(SQL_Variant, [OrderDate])
402图片 413                ELSE
403图片 414                    NULL
404图片 415            END AS [AscendingSort]
405图片 416        FROM
406图片 417            Sales.SalesOrderHeader Header
407图片 418        WHERE
408图片 419            Header.[OrderDate] >= @orderedOnStart
409图片 420            AND Header.[OrderDate] < @orderedOnEnd
410图片 421    ) Derived
411图片 422    WHERE
412图片 423    (
413图片 424        @lastAscendingSortValue IS NULL
414图片 425        AND @lastDescendingSortValue IS NULL
415图片 426        AND @lastKeyValue IS NULL
416图片 427    )
417图片 428    OR
418图片 429    (
419图片 430        (@lastAscendingSortValue IS NOT NULL)
420图片 431        AND
421图片 432        (
422图片 433            ([AscendingSort] > @lastAscendingSortValue)
423图片 434            OR
424图片 435            (
425图片 436                [AscendingSort] = @lastAscendingSortValue
426图片 437                AND [UniqueValue] > @lastKeyValue
427图片 438            )
428图片 439        )
429图片 440    )
430图片 441    OR
431图片 442    (
432图片 443        (@lastDescendingSortValue IS NOT NULL)
433图片 444        AND
434图片 445        (
435图片 446            ([DescendingSort] < @lastDescendingSortValue)
436图片 447            OR
437图片 448            (
438图片 449                [DescendingSort] = @lastDescendingSortValue
439图片 450                AND [UniqueValue] > @lastKeyValue
440图片 451            )
441图片 452        )
442图片 453    )
443图片 454    ORDER BY
444图片 455        [AscendingSort] ASC,
445图片 456        [DescendingSort] DESC,
446图片 457        [SalesOrderID] ASC
447图片 458
448图片 459    SET ROWCOUNT 0
449图片 460GO
450图片 461
451图片 462
452图片 463————-4分页
453图片 464
454图片 465SET QUOTED_IDENTIFIER ON
455图片 466GO
456图片 467SET ANSI_NULLS ON
457图片 468GO
458图片 469
459图片 470    ALTER  PROCEDURE sp_Easypaging
460图片 471
461图片 472    (
462图片 473   
463图片 474    @Tables varchar(1000),
464图片 475    @PK varchar(100),
465图片 476    @JoinStatements varchar(1000)=”,
466图片 477    @Fields varchar(5000) = ‘*’,
467图片 478    @Filter varchar(5000) = NULL,
468图片 479    @Sort varchar(200) = NULL,
469图片 480    @PageNumber int = 1,
470图片 481    @PageSize int = 10,
471图片 482    @TotalRec int =0 Output,
472图片 483    @Group varchar(1000) = NULL
473图片 484   
474图片 485       
475图片 486    )
476图片 487
477图片 488AS
478图片 489
479图片 490
480图片 491
481图片 492图片 493/**//*
482图片 494Created by Kashif Akram
483图片 495Email Muhammad_kashif@msn.com
484图片 496
485图片 497The publication rights are reserved 
486图片 498You can use this procedure with out removing these comments
487图片 499*/
488图片 500
489图片 501
490图片 502
491图片 503DECLARE @strPageSize varchar(50)
492图片 504DECLARE @strStartRow varchar(50)
493图片 505
494图片 506
495图片 507SET @strPageSize = CAST(@PageSize AS varchar(50))
496图片 508SET @strStartRow = CAST(((@PageNumber – 1)*@PageSize + 1) AS varchar(50))
497图片 509
498图片 510
499图片 511
500图片 512–set @PK =’ tbl_Items.ItemID ‘
501图片 513
502图片 514create table #PageTable (PID  bigint primary key IDENTITY (1, 1) , UID int)
503图片 515create table #PageIndex (UID int)
504图片 516
505图片 517图片 518/**//*
506图片 519CREATE UNIQUE CLUSTERED
507图片 520  INDEX [PK_tbl_PageTable] ON #PageTable (PID)
508图片 521*/
509图片 522CREATE 
510图片 523  INDEX [PK_tbl_PageIndex] ON #PageIndex (UID)
511图片 524
512图片 525
513图片 526–‘SELECT ‘ + @Fields + ‘ FROM ‘ + @Tables + ” + @JoinStatements +’ WHERE ‘ + @strSortColumn + @operator + ‘ @SortColumn ‘ + @strSimpleFilter + ‘ ‘ + @strGroup + ‘ ORDER BY ‘ + @Sort +  ‘ DESC ‘
514图片 527exec (‘
515图片 528
516图片 529set rowcount 0
517图片 530
518图片 531    insert into #pageTable(UID)
519图片 532     SELECT ‘ + @PK + ‘ FROM ‘ + @Tables + ‘ ‘ + @JoinStatements +’ WHERE ‘ +  @Filter + ‘ ‘ + @Group + ‘ ORDER BY ‘ + @Sort +  ‘
520图片 533
521图片 534
522图片 535DECLARE @SortColumn int
523图片 536
524图片 537SET ROWCOUNT ‘+  @strStartRow +’
525图片 538
526图片 539select  @SortColumn=PID from #PageTable –option (keep plan)
527图片 540
528图片 541print @SortColumn
529图片 542
530图片 543SET ROWCOUNT ‘+  @strPageSize +’
531图片 544
532图片 545insert into #pageIndex
533图片 546select UID from #PageTable where PID >= @SortColumn — option (keep plan)
534图片 547
535图片 548
536图片 549         
537图片 550SELECT ‘ + @Fields + ‘ FROM ‘ + @Tables + ‘ ‘ + @JoinStatements +’ WHERE ‘ +  @Filter + ‘ and  ‘+ @PK + ‘ in (Select UID from #pageIndex)’ + @Group + ‘ ORDER BY ‘ + @Sort +  ‘ ‘
538图片 551
539图片 552 )
540图片 553
541图片 554
542图片 555
543图片 556select @TotalRec=count(*) from  #pageTable
544图片 557
545图片 558
546图片 559
547图片 560
548图片 561
549图片 562drop table #PageTable
550图片 563drop table #PageIndex
551图片 564
552图片 565
553图片 566   
554图片 567RETURN
555图片 568
556图片 569GO
557图片 570SET QUOTED_IDENTIFIER OFF
558图片 571GO
559图片 572SET ANSI_NULLS ON
560图片 573GO

发表评论

电子邮件地址不会被公开。 必填项已用*标注