Question

求一个好用的asp.net分页存储过程 发到我邮箱，谢谢！！

Answer 1

IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords')
DROP PROCEDURE cndoup_GetPageOfRecords
GO
--创建存储过程
CREATE PROCEDURE cndoup_GetPageOfRecords
@pageSize int = 20,--分页大小  
@currentPage int , --第几页  
@columns varchar(1000) = '*',--需要得到的字段   
@tableName varchar(100), --需要查询的表      
@condition varchar(1000) = '', --查询条件,不用加where关键字  
@ascColumn varchar(100) = '',--排序的字段名 (即 order by column asc/desc) 
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)  
@pkColumn varchar(50) = '' --主键名称
AS
BEGIN --存储过程开始  
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000) --该存储过程最后执行的语句  
DECLARE @strOrderType varchar(1000)--排序类型语句 (order by column asc或者order by column desc)  

BEGIN
IF @bitOrderType = 1--降序      
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE--升序      
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END

IF @currentPage = 1--第一页      
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '
+@tableName+' WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+'
FROM '+@tableName+@strOrderType
END
ELSE-- 其他页      
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '
+@tableName+' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn
+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tableName+' where '+@condition+@strOrderType+')
AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '
+@tableName+' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+'
FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+'
FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END

EXEC cndoup_GetPageOfRecords 3,2,'*','Users','','UserId',0,'UserId'

Answer 2

好东西要分享的，把代码放这让大家都看到把
01 CREATE PROCEDURE search_sp
02 @tblName varchar(255), -- 表名
03 @strGetFields varchar(1000) = '*', -- 需要返回的列
04 @fldName varchar(255)='', -- 排序的字段名
05 @PageSize int = 10, -- 页尺寸
06 @PageIndex int = '', -- 页码
07 @doCount bit = 0, -- 返回记录总数, 非 0 值则返回
08 @OrderType bit = '', -- 设置排序类型, 非 0 值则降序
09 @strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
10 AS
11
12 declare @strSQL nvarchar(4000) -- 主语句
13 declare @strTmp varchar(110) -- 临时变量
14 declare @strOrder varchar(400) -- 排序类型
15 if @doCount != 0
16 begin
17 if @strWhere !=''
18 set @strSQL = 'select count(1) as Total from [' + @tblName + '] where '+@strWhere
19 else
20 set @strSQL = 'select count(1) as Total from [' + @tblName + ']'
21 end
22 --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
23
24 else
25 begin
26 if @OrderType != 0
27 begin
28 set @strTmp = '< (select min'
29 set @strOrder = ' order by [' + @fldName +'] desc'
30 --如果@OrderType不是0，就执行降序，这句很重要！
31 end
32
33 else
34 begin
35 set @strTmp = '> (select max'
36 set @strOrder = ' order by [' + @fldName +'] asc'
37 end
38 if @PageIndex = 1
39 begin
40 if @strWhere != ''
41 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
42 else
43 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName +'] '+ @strOrder
44 --如果是第一页就执行以上代码，这样会加快执行速度
45 end
46
47 else
48 begin
49 --以下代码赋予了@strSQL以真正执行的SQL代码
50 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
51 + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']'+ @strOrder + ') as tblTmp)'+ @strOrder
52 if @strWhere != ''
53 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
54 + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
55 + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
56 + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
57 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
58 end
59 end
60 exec SP_EXECUTESQL @strSQL
61
62 GO

Answer 3

邮箱地址呢

追问

223515420@qq.com