Sql Server存储过程

--创建数据库
create database Month01
go
use Month01

--创建班级表
create table BanJi
(
BId int identity primary key,    --班级编号
BName varchar(20),                --班级名称
)
--添加数据
insert into BanJi values('1711A')
insert into BanJi values('1711B')

--创建学生信息表
create table XinXi
(
Id        int identity primary key,                --学生编号
Name    varchar(20),                            --学生姓名
XId        int foreign key references BanJi(BId),    --所在班级
RTime    datetime,                                --入学时间
Age        int,                                    --年龄
Sex        bit,                                    --性别 0.男 1.女
)

--添加数据
insert into XinXi values('马胜昆','1','2016/07/25','20','0')
insert into XinXi values('李西水','1','2017/07/25','20','0')
insert into XinXi values('郭帅','1','2015/09/13','20','0')
insert into XinXi values('小梅','1','2016/08/01','20','1')
insert into XinXi values('小美','1','2017/07/21','20','1')
insert into XinXi values('小兰','2','2018/03/12','21','1')
insert into XinXi values('笑笑','2','2018/03/12','21','1')
insert into XinXi values('李国才','2','2016/08/01','20','0')
insert into XinXi values('李耀科','2','2017/07/21','20','0')
insert into XinXi values('张信哲','2','2016/02/02','35','0')
--两表联查
select * from XinXi x join BanJi b on x.XId=b.BId

--删除
delete from XinXi where Id=10


--存储过程分页 查询

go
create proc P_Show
(
    --查询条件
    @Name nvarchar(100),
    @RTime nvarchar(100),
    --分页的条件
    @PageIndex int,
    @PageSize int,
    @PageCount int out --输出参数
)
as
Begin

    declare @sqlWhere nvarchar(max)='',@sqlPageed nvarchar(max)=''
    
    if @Name!=''
        set @sqlWhere+=' and Name like ''%'+@Name+'%'''
    
    if @RTime!=''
        set @sqlWhere+=' and RTime='''+@RTime+''''
    
--分页
--===========================================================================================================    
    
    set @sqlPageed='select top('+cast(@PageSize as nvarchar(100))+') * from 
    (select ROW_NUMBER() over(order by x.Id) rowIndex,o.*,x.Name from XinXi x join BanJi b on x.XId=b.BId where 1=1 '+@sqlWhere+' ) t
    where rowIndex>'+cast((@PageIndex-1)*@PageSize as nvarchar(100))
    
    exec sp_executesql @sqlPageed
--=========================================================================================================
--获取总页数
--=========================================================================================================
declare @sqlCount nvarchar(max)=''

    set @sqlCount='select @PageCount=COUNT(1) from XinXi x join BanJi b on x.XId=b.BId where 1=1'+@sqlWhere

    exec sp_executesql @sqlCount,N'@PageCount int out',@PageCount out    
End

--添加存储过程
alter proc P_Add
(
@YTime varchar(20),
@XId int,
@Yint int,
@YName varchar(20)
)
as
begin

    set nocount on
    set xact_abort on
    declare @sql1 int=0,@sql2 int=0
    begin tran
    insert into YuDing values(@YTime,@XId,@Yint,@YName)
    set @sql2=@@IDENTITY
    set @sql1+=@@ERROR
    if @sql1<>0
    begin
        select 0 as Num
        rollback tran
    end
    else
    begin
        select 1 as Num
        commit tran
    end

end


--创建删除存储过程
alter proc P_Del
(
@YId int
)
as
begin
    set nocount on
    set xact_abort on
    --declare @sql1 int=0, @sql2 int=0

    begin tran

    select @sql2=Yint from YuDing where YId=@YId
    update XinXi set Xint=Xint-@sql2 where XId=(select XId from YuDing WHERE YId=@YId)
    SET @sql1+=@@ERROR

    update LeiXin set Lint=Lint+@sql2 where LId=(select LId from XinXi where XId=(select XId from YuDing where YId=@YId))
    set @sql1+=@@ERROR

    --delete from YuDing where YId=@YId
    --set @sql1+=@@ERROR

    if @sql1 <> 0
    begin 
        select 0 as Num
        rollback tran
    end
    else
    begin
        select 1 as Num
        commit tran
    end
end

exec P_Del 12