--无参数存储过程
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 orAge=@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