create proc pindex ( @index int, @size int, @total int output, @pagecount int output ) as begin declare @sqlstr nvarchar(1000)
select @total=count(*) from Star if(@total%@size=0) set @pagecount=@total/@size else set @pagecount=@total/@size+1 if(@index=1) set @sqlstr='select top '+str(@size)+' * from Star' else set @sqlstr='select top '+str(@size)+' * from Star where Sid not in (select top '+str(@size*(@index-1))+' Sid from Star)'exec (@sqlstr) endexec pindex 2,5,0,0
我想要一个完整的例子,包括在aps.net调用存储过程的。。要可以查询,可以排序的。
select * from a inner join b on a.userID=b.userID把 * 给改下。 我这有现成的多表查询分页,不过写的比较复杂
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[MyListPage] @tblName varchar(5000), -- 表名 @fldName varchar(5000) = '*', -- 字段名(全部字段为*) @strCondition nvarchar(max) = NULL, -- 条件语句(不加 where,可带 group by 分组条件,分组条件需要带 group by 关键字) @fldSort varchar(5000), -- 排序字段(必须,支持多字段,不加 order by) @pageSize int, -- 页大小(每页多少条记录) @page int = 1 , -- 指定当前为第几页 @pageCount int = 1 output, -- 返回总页数 @Counts int =1 output, @Sort int = 1, @keyID nvarchar(50)='ID', @IsDis int = 0 AS BEGIN Begin Tran -- 开始事务 Declare @sql nvarchar(max); Declare @totalRecord int; Declare @sqlSort nvarchar(200) ----存放临时生成的排序条件 if @Sort=0 --升序 begin if not(@fldSort is null or @fldSort = '') set @sqlSort = ' Order by ' +@fldSort else set @sqlSort = ' Order by ' +@keyID end else --降序 begin if not (@fldSort is null or @fldSort='') set @sqlSort = ' Order by '+ @fldSort + ' Desc' else set @sqlSort = ' Order by '+ @keyID + ' Desc ' end -- 计算总记录数 if (@strCondition = '' or @strCondition = NULL) set @sql = 'select @totalRecord = count(0) from ' + @tblName else BEGIN if(CHARINDEX('group by', LOWER(@strCondition)) > 0) set @sql = 'select @totalRecord = count(0) from (select ' + @fldName + ' from ' + @tblName+ ' where ' + @strCondition + ') as Tab_GroupTable' else set @sql = 'select @totalRecord = count(0) from ' + @tblName+ ' where ' + @strCondition END --print @Sql EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT -- 计算总页数 select @pageCount=CEILING((@totalRecord+0.0)/@pageSize) -- 处理页数超出范围情况 if @page<=0 Set @page = 1 if @page>@pageCount Set @page = @pageCount -- 处理开始点和结束点 Declare @startRecord int Declare @endRecord int set @startRecord = (@page-1)*@pageSize + 1 set @endRecord = @startRecord + @pageSize - 1 -- 合成sql语句 if (@strCondition = '' or @strCondition = NULL) set @sql = 'Select * FROM (select ' + @fldName + ', ROW_NUMBER() Over(' + @sqlSort + ') as rowId from ' + @tblName else set @sql = 'Select * FROM (select ' + @fldName+', ROW_NUMBER() Over('+ @sqlSort + ') as rowId from ' + @tblName+ ' where ' + @strCondition set @Sql = @Sql + ') as Tab_TotalTable where rowId between ' + Convert(varchar(50),@startRecord) + ' and ' + Convert(varchar(50),@endRecord) ---判断是否加 消除重复行 if(@IsDis<>0) begin set @Sql = @Sql + ' order by ID Desc' end Exec(@Sql) if @@Error <> 0 BEGIN RollBack Tran Return -1 END else BEGIN Commit Tran set @Counts=@totalRecord --- 返回记录总数 END END
ROW_NUMBER() 在SQL2000中也没有啊。。
select top 15 * from template where (ID>(select max(ID) from (select top 30 ID from template order by ID asc) as T)) order by ID asc
CREATE PROCEDURE UP_GetRecordByPageOrder
@tblName varchar(255), -- 表名 @fldName varchar(255), -- 显示字段名 @OrderfldName varchar(255), -- 排序字段名 @StatfldName varchar(255), -- 统计字段名 @PageSize int = 1, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(100) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @OrderfldName +'] desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @OrderfldName +'] asc' end set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from [' + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '([' + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from [' + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '([' + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from [' + @tblName + ']' + @strTmp + ' ' + @strOrder end if @IsReCount != 0 set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'if @strWhere!='' set @strSQL = @strSQL+' where ' + @strWhere exec (@strSQL) GO 这个是我本来的存储过程??怎么能多表查询??
比如有a,b两表。
就是a.userID=b.userID就行。
create proc pindex
(
@index int,
@size int,
@total int output,
@pagecount int output
)
as
begin
declare @sqlstr nvarchar(1000)
select @total=count(*) from Star if(@total%@size=0)
set @pagecount=@total/@size
else
set @pagecount=@total/@size+1 if(@index=1)
set @sqlstr='select top '+str(@size)+' * from Star'
else
set @sqlstr='select top '+str(@size)+' * from Star where Sid not in (select top '+str(@size*(@index-1))+' Sid from Star)'exec (@sqlstr)
endexec pindex 2,5,0,0
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[MyListPage]
@tblName varchar(5000), -- 表名
@fldName varchar(5000) = '*', -- 字段名(全部字段为*)
@strCondition nvarchar(max) = NULL, -- 条件语句(不加 where,可带 group by 分组条件,分组条件需要带 group by 关键字)
@fldSort varchar(5000), -- 排序字段(必须,支持多字段,不加 order by)
@pageSize int, -- 页大小(每页多少条记录)
@page int = 1 , -- 指定当前为第几页
@pageCount int = 1 output, -- 返回总页数
@Counts int =1 output,
@Sort int = 1,
@keyID nvarchar(50)='ID',
@IsDis int = 0
AS
BEGIN
Begin Tran -- 开始事务 Declare @sql nvarchar(max);
Declare @totalRecord int;
Declare @sqlSort nvarchar(200) ----存放临时生成的排序条件
if @Sort=0 --升序
begin
if not(@fldSort is null or @fldSort = '')
set @sqlSort = ' Order by ' +@fldSort
else
set @sqlSort = ' Order by ' +@keyID
end
else --降序
begin
if not (@fldSort is null or @fldSort='')
set @sqlSort = ' Order by '+ @fldSort + ' Desc'
else
set @sqlSort = ' Order by '+ @keyID + ' Desc '
end
-- 计算总记录数
if (@strCondition = '' or @strCondition = NULL)
set @sql = 'select @totalRecord = count(0) from ' + @tblName
else
BEGIN
if(CHARINDEX('group by', LOWER(@strCondition)) > 0)
set @sql = 'select @totalRecord = count(0) from (select ' + @fldName + ' from ' + @tblName+ ' where ' + @strCondition + ') as Tab_GroupTable'
else
set @sql = 'select @totalRecord = count(0) from ' + @tblName+ ' where ' + @strCondition
END --print @Sql EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT -- 计算总页数
select @pageCount=CEILING((@totalRecord+0.0)/@pageSize) -- 处理页数超出范围情况
if @page<=0
Set @page = 1
if @page>@pageCount
Set @page = @pageCount -- 处理开始点和结束点
Declare @startRecord int
Declare @endRecord int
set @startRecord = (@page-1)*@pageSize + 1
set @endRecord = @startRecord + @pageSize - 1 -- 合成sql语句
if (@strCondition = '' or @strCondition = NULL)
set @sql = 'Select * FROM (select ' + @fldName + ', ROW_NUMBER() Over(' + @sqlSort + ') as rowId from ' + @tblName
else
set @sql = 'Select * FROM (select ' + @fldName+', ROW_NUMBER() Over('+ @sqlSort + ') as rowId from ' + @tblName+ ' where ' + @strCondition set @Sql = @Sql + ') as Tab_TotalTable where rowId between ' + Convert(varchar(50),@startRecord) + ' and ' + Convert(varchar(50),@endRecord)
---判断是否加 消除重复行
if(@IsDis<>0)
begin
set @Sql = @Sql + ' order by ID Desc'
end Exec(@Sql) if @@Error <> 0
BEGIN
RollBack Tran
Return -1
END
else
BEGIN
Commit Tran
set @Counts=@totalRecord --- 返回记录总数
END
END
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 显示字段名
@OrderfldName varchar(255), -- 排序字段名
@StatfldName varchar(255), -- 统计字段名
@PageSize int = 1, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @OrderfldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @OrderfldName +'] asc'
end set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'if @strWhere!=''
set @strSQL = @strSQL+' where ' + @strWhere
exec (@strSQL)
GO
这个是我本来的存储过程??怎么能多表查询??