ALTER PROCEDURE [dbo].[UP_GetShopPage]
@tblName nvarchar(200) --表名
,@fldName varchar(255) -- 主键字段名
,@fldNames nvarchar(1000) --结果字段
,@ordNames nvarchar(200) --排序字段
,@PageSize int = 10
,@PageIndex int = 1
,@IsReCount bit = 0 -- 返回记录总数, 非 0 值则返回
,@OrderType bit = 0 -- 设置排序类型, 非 0 值则降序
,@strWhere nvarchar(max) = ''
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max) -- SQL to execute
,@recct int -- total # of records (for GridView paging interface)
,@strOrder nvarchar(255) if @OrderType != 0
begin
set @strOrder = ' order by ' + @ordNames +' desc'
end
else
begin
set @strOrder = ' order by ' + @ordNames +' asc'
end
IF LTRIM(RTRIM(@strWhere)) = '' SET @strWhere = '1 = 1' declare @temp nvarchar(max)
set @temp='if exists(select * from tempdb..sysobjects where id=object_id(''tempdb..#t''))
begin drop table #t end select identity(int,1,1) as r,* into #t from(
select * from (select top(select count(*) from shop)* from shop where publiccount>0 and '+ @strWhere+@strOrder+') as a
union all
select * from shop where publiccount=0 and '+@strWhere+' ) as b' IF @PageSize IS NULL BEGIN
SET @STMT =@temp+'SELECT ' + @fldNames +
'FROM #t'
EXEC (@STMT) -- return requested records
END ELSE BEGIN
DECLARE
@lbound int,
@ubound int SET @PageIndex = ABS(@PageIndex)
SET @PageSize = ABS(@PageSize)
IF @PageIndex < 1 SET @PageIndex = 1
IF @PageSize < 1 SET @PageSize = 1
SET @lbound = ((@PageIndex - 1) * @PageSize)
SET @ubound = @lbound + @PageSize + 1 SET @STMT = @temp+ ' SELECT ' + @fldNames + '
FROM (
SELECT ROW_NUMBER() OVER ( order by r asc) AS row, *
FROM #t
) AS tbl
WHERE
row > ' + CONVERT(varchar(9), @lbound) + ' AND
row < ' + CONVERT(varchar(9), @ubound)
EXEC (@STMT) -- return requested records
END
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
drop table #t
解决方案 »
- AspNetAjaxPager更新,谢谢各位拍砖的朋友,现在推出更完善的版本!谢谢支持,支持的都加分!
- 怎样控制GridView使其中的删除按钮有的行显示,有的行不显示?
- 用户登陆时如何判断用户身份
- 急,updatepanel的一个问题???
- (在线等)Response.OutputStream.Write输出pdf文档时,如何实现提示用户正在加载
- ASP.NET的treeview如何在节点右边显示添加 更新 删除 按钮呢?
- 求救:关于在文本框中输入‘<'字符出错的问题,请高手进来看看!
- 焦点问题,高手相救
- C#ASP.NET如何 调出那个就象对话框设计一样的网页设计器?不出现光标,可以自由定位控件
- asp.net问题,初学者100分求教
- JavaScriptSerializer 序列化json数据时,如何设置不序列化复杂类型
- .NET存储过程的代码
既然进来看了 就帮帮忙吧
既然进来看了 就帮帮忙吧