触发器代码

exec sp_databases
declare @Studnet int
set @Studnet=243
exec sp_helptext @Studnet
exec sp_helptext 存储过程名

--创建
create proc usp_GetStudentById
@Id int
as
select * from Student where id=@Id

alter proc usp_GetStudentByIdAndName
@Id int,
@Name varchar(10)   --长度
as
select * from Student where id=@Id and name=@Name

alter proc usp_GetStudent       --修改  alter
as
begin
select * from Student where id='1'
select * from Student
end

--分页1
alter proc usp_GetPageStudnetRowNum

@pageindex int=1
as
begin
   select * from
(
   select *,ROW_NUMBER() over(order by ID) AS rownum
   from Student) as a
   where a.rownum >(@pageindex-1)*5 and a.rownum<= @pageindex*5
end

exec usp_GetPageStudnetRowNum 2

--分页2
create proc usp_GetPageStudnetRowNum2

@pageindex int=1,
@countnum int=5
as
begin
   select * from
(
   select *,ROW_NUMBER() over(order by ID) AS rownum
   from Student) as a
   where a.rownum >(@pageindex-1)*@countnum and a.rownum<= @pageindex*@countnum
end

exec usp_GetPageStudnetRowNum2    --全默认
exec usp_GetPageStudnetRowNum2 @countnum=3
exec usp_GetPageStudnetRowNum2 @pageindex=3,@countnum=3

--输入参数
declare @p int,@c int
set @p=2
set @c=3
exec usp_GetPageStudnetRowNum2 @p,@c

alter proc usp_GetPageStudnetRowNum3

@pageindex int=1,
@countnum int=5,
@rowcount int output
as
begin
   select * from
(
   select *,ROW_NUMBER() over(order by ID) AS rownum
   from Student) as a
   where a.rownum >(@pageindex-1)*@countnum and a.rownum<= @pageindex*@countnum
   select @rowcount=COUNT(*) from Student
   set @pageindex=234
end

--输出
declare @p int,@c int,@co int
set @p=2
set @c=3
set @co=0
exec usp_GetPageStudnetRowNum3 @p,@c,@co output

select @co

alter proc usp_GetPageStudnetRowNum4

@pageindex int=1,
@countnum int=5,
@rowcount int output,
@pagecount int output
as
begin
   select * from
(
   select *,ROW_NUMBER() over(order by ID) AS rownum
   from Student) as a
   where a.rownum >(@pageindex-1)*@countnum and a.rownum<= @pageindex*@countnum
   select @rowcount=COUNT(*) from Student
--   set @pagecount=@rowcount/@countnum
 set @pagecount=CEILING(CONVERT(float,@rowcount)/CONVERT(float,@countnum))
end

declare @p int,@c int,@co int,@coun int
set @p=2
set @c=3
set @co=0
exec usp_GetPageStudnetRowNum4 @pageindex=@p,@countnum=@c,@rowcount=@co output,@pagecount=@coun output

select @p,@c,@co,@coun as i

--调用
exec sp_helptext usp_GetStudentById

exec usp_GetStudentById '1'   --参数

exec usp_GetStudentByIdAndName @Id='5',@Name='few'    --多个参数

exec usp_GetStudent

--销毁
drop proc usp_GetStudentById


--
select * from
(
select *,ROW_NUMBER() over(order by ID) AS rownum
from Student
) as a
where a.rownum<=5

delete from Student
drop table Student