存储过程的使用

--无参数存储过程
create proc proUserInfo
as
select * from T_UserInfo
go
exec proUserInfo   --执行存储过程

--创建带输入参数的存储过程
create proc proUserInfo1
@name varchar(20)
as
select * from T_UserInfo where Name=@name
go

exec proUserInfo1 '李哲'

--创建带2个输入参数的存数过程
create proc proUserInfo2
@name varchar(20),
@age int
as
select * from T_UserInfo where Name=@name or Age=@age
go

exec proUserInfo2 '李哲',18

--创建带输入参数和输出参数的存储过程
create proc proUserInfo3
@name varchar(20),
@age int output
as
select @age=age from T_UserInfo where Name=@name
go

begin
declare @age int,
exec proUserInfo3
end

--分页
select * from (select ROW_NUMBER() over(order by UserId) as rownum, * from T_UserInfo) t where rownum>3 and rownum<=6

--存储过程分页
create proc profenye
@pagesize int,
@pageindex int
as
select * from (select ROW_NUMBER() over(order by UserId) as rownum, * from T_UserInfo) t where
 rownum>(@pageindex-1)*@pagesize
 and rownum<=@pageindex*@pagesize
 go
 
 --调用存储过程分页
exec profenye 4,1

--创建索引
create index index_userinfo