分页存储过程

 

----------建立表----------

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 '%'+'我们一起沉 
默'+'%'