----------建立表----------
if exists(select * from sys.objects where object_id = object_id (N'
[DBO].
[TERMINATOR]') and type
in (N'U'
))
DROP TABLE [DBO].[TERMINATOR]
GO
CREATE TABLE TERMINATOR
(ID INT IDENTITY (1,1) PRIMARY KEY ,NAME VARCHAR(30
))
GO
-----------存储过程 分页存储----------
create procedure [dbo].[T]
@pageSize int =1
,
@pageIndex int = 1
,
@where varchar(8000) = ' 1=1 '
,
@sortField varchar(100) = ' [id] desc '
as
set nocount on
declare @terrific varchar(8000
)
set @terrific = 'select top '+convert(varchar(8),@pageSize)+'
[id],[name],[time] from
[dbo].[TERMINATOR]
where [id] not in (select top '+ CONVERT(VARCHAR(8),@pageSize * (@pageIndex - 1) ) +
'[id] from [dbo].[TERMINATOR] WHERE '
+ @WHERE + ' ORDER BY ' + @SORTFIELD + ') and '+ @where + ' order by ' +
@sortfield
----------
print @TERMINATOR xp
exec (@terrific)
RETURN @@ERROR
-----------------------页面大小----------显示页面----------条件---------排序----------
exec T 3, 3, '1=1', 'id asc'
---------循环添加----------
declare @a int,@b
int
set @a=1
while @a<1000000
begin
insert into TERMINATOR(name,TIME) values('vista',2009-1-1
)
set @a=@a + 1
end
----------索引查询----------
select top 10 *
from terminator
select top 100 name from TERMINATOR WITH (INDEX=TT) WHERE NAME LIKE '%'+'
我们一起沉
默'+'%'
View Code
转载于:https://www.cnblogs.com/siyunianhua/archive/2011/12/26/2302686.html
相关资源：高效分页存储过程高效分页存储过程高效分页存储过程
