存储过程的入门使用

无参数存储过程

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

</pre></p><p></p><p style="color: rgb(54, 46, 43); line-height: 26px; font-family: Arial; font-size: 14px;"><span style="font-size: 16px;">--创建带输入参数的存储过程</span><pre class="sql" name="code" snippet_file_name="blog_20150109_3_9066877" code_snippet_id="575802">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