5.3 新建读取团员信息的存储过程

 新建读取团员信息的存储过程

 

1 点击New Stored Procedure...

 

2 存储过程语法为

Create PROCEDURE [dbo].[sp_getPageTeamInfo]

 @page int,

 @pageSize int,

 @lastPage int out

AS

BEGIN

 declare @total int, @begin int, @end int;

 set @total = (select count(id) from TeamManager.dbo.team);

 if @total <= 0

 begin

  set @lastPage = 0;

 end

 else

 begin

  set @lastPage = ceiling((@total+0.0)/@pageSize);

  if @page > @lastPage

  begin

   set @page = @lastPage;

  end

  set @begin = (@page-1)*@pageSize + 1;

  set @end = @page * @pageSize;

  select * from(

   select row_number() over(order by id) r, * from TeamManager.dbo.team

  ) t where r between @begin and @end;

 end

END

 

3 点击Execute，执行新建

 

显示 successfully，则表示新建成功

 

选中Stored Procedures目录，按下F5键刷新后，能看见新建的存储过程