存储过程

if exists(select * from sys.objects where name='usp_AddClass')

drop proc usp_AddClass
go

--上面的语句是用来检查数据库中是否有名为usp_AddClass的存储过程

--如果有删除重新创建
create proc usp_AddClass
@name varchar(50),
@count int,
@result int output
as
insert into dbo.Classes(CName,CCount) values(@name,@count)
--@@identity 最大的自动编号
set @result = @@identity

--执行存储过程

declare @r int
exec usp_AddClass 'abc',70,@r output
print @r

select * from classes

 

--删除
if exists(select * from sys.objects where name='usp_DeleteClass')
drop proc usp_DeleteClass
go
create proc usp_DeleteClass
@id int
as
delete from classes where cid = @id

--返回受影响的行数
select @@rowcount

 

exec usp_DeleteClass 12

--查询

if exists(select * from sys.objects where name='usp_SelectClasses')
drop proc usp_SelectClasses
go

create proc usp_SelectClasses
as
select * from classes

exec usp_SelectClasses

--分页存储过程
if exists(select * from sys.objects where name='usp_PageData')
drop proc usp_PageData
go
create proc usp_PageData
@pageIndex int, --pageIndex当前第几页
@pageSize int, --pageSize每页多少条
@pageCount int output --pageCount总共多少页 输出参数
as
--总页数
select @pageCount = ceiling(count(*)/(@pagesize*1.0)) from classes
select * from
(select *,row_number() oven br(order by cAddTime desc) as num from classes) as t
where num between (@pageIndex-1)*@pageSize+1 and @pageSize*@pageIndex order by cAddTime desc