分页存储过程

--------------------建立存储过程  无参数---------------------------------------
if exists (select * from sysobjects where id = object_id(N'[usertablesw]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [usertablesw]
go
 create proc usertablesw
    as
    select id as '编号',name as '姓名' from login



    -- 调用
    exec usertablesw


--------------------建立存储过程  有参数   输入参数---------------------------------------
create proc linkeselect
    @name varchar(20)
    as
    select * from login where name like '%'+@name+'%'



create proc updatedb
    @name varchar(20)
    as
     update login set name=@name where id=1


create proc deletedb
    @name int
    as
    delete login where id=@name


create proc insertdb
    @name varchar(20)
    as
    insert into login(name) values(@name)


-- 调用模糊查询存储过程
exec  linkeselect
--调用更新
exec  updatedb '中华人民共和国'
--删除
exec  deletedb 1
--添加
exec insertdb '中华人民共和国'

--wm查询
alter proc selectwm
@wm varchar(50)
as
select pwd from wm where pwd like '%'+@wm+'%'
--调用
exec selectwm'1'


--wm更改
alter proc updatewm
@hh int
as
update wm set pwd=@hh where id=2
--调用
exec updatewm 66




--wm更改多个参数
alter proc updatewm
@hh int,
@ff varchar(50)
as
update wm set pwd=@hh,name=@ff where id=2
--调用
exec updatewm 778788788,'哈7哈'


--wm添加一个参数
alter proc insertwm

as
insert into wm(pwd,name) values(2,'话')
select* from wm
--调用
exec insertwm


--添加两个参数

alter proc insterwm2
@ww varchar(50),
@tt int
as

insert into wm (name,pwd) values(@ww,@tt)
--调用
exec insterwm2'好吗',23


--------------------建立存储过程  分页存储过程---------------------------------------

    
CREATE PROCEDURE [dbo].[ACImageTableSelectByPagerParams]
@pageSize int =3,
@pageIndex int = 1,
@where varchar(8000) = ' 1=1 ',
@sortField varchar(100) = ' [id] DESC '
AS
    SET NOCOUNT ON
    declare @filter varchar(8000)    
    set @filter = '
        SELECT TOP ' + CONVERT(VARCHAR(8),@pageSize) + '
        [id],
        [images],
        [title],
        [contents],
        [time],
        [messa]
FROM [dbo].[ACImageTable]
where [id] not in (SELECT TOP ' + CONVERT(VARCHAR(8),@pageSize * (@pageIndex - 1))
+ ' [id] FROM [dbo].[ACImageTable]
WHERE ' + @where + ' ORDER BY ' + @sortField + ' ) AND ' + @where + ' ORDER BY ' + @sortField
    --print @filter
    exec ( @filter )
    RETURN @@Error


------------------------------------   页面大小-----显示页面------条件-----排序
exec ACImageTableSelectByPagerParams    3,              3,        '1=1',  'id asc'

--------------------循环添加---------------------------------------
declare @a int,@b int
set @a=1
set @b=1
while @a<1000000
begin
   insert into wm (name,pwd) values('我们一起沉默',@b)
   set @a=@a+1
 --  set @b=(@a+1)*5-2
end
--------------------索引查询---------------------------------------

select top 500000 * from wm
select  top 500000 name from wm with(index=tcss) where name like '%'+'中'+'%'
delete wm