WebService三表存储过程

create database LianXi728
use LianXi728
create table School
(
SchoolId int primary key identity,
SchoolCode int,
SchoolName varchar(50)
)
create table Class
(
ClassId int primary key identity,
ClassCode int,
SchoolCode int,
ClassName varchar(50)
)
create table Student
(
StudentId int primary key identity,
ClassCode int,
StudentName varchar(50),
Sex int
)
———————————存储过程—————————————
———————————分页查询—————————————
alter proc p_GetStudentList
(
@SchoolCode varchar(100),
@ClassCode varchar(100),
@StudentName varchar(50),
@PageIndex int,
@PageSize int,
@PageCount int output
)
as
begin
begin tran
begin try
declare @subsql nvarchar(max)
set @subsql=’select a.StudentId,a.StudentName,a.Sex,b.ClassCode,b.ClassName,c.SchoolCode,c.SchoolName,
ROW_NUMBER() over(order by StudentId) as rownumber from Student as a
inner join Class as b on a.ClassCode=b.ClassCode
inner join School as c on b.SchoolCode=c.SchoolCode where 1=1’;
if(@SchoolCode!=”)
set @subsql+=’ and c.SchoolCode=”’+@SchoolCode+””;
if(@ClassCode!=”)
set @subsql+=’ and b.ClassCode=”’+@ClassCode+””;
if(@StudentName!=”)
set @subsql+=’ and a.StudentName like ”%’+@StudentName+’%”’;
declare @sql nvarchar(max)
set @sql=’select * from (‘+@subsql+’) as p where rownumber between’+str((@PageIndex-1)@PageSize+1)+’and’+str((@PageIndex@PageSize))
exec (@sql)
declare @totalPage float
exec (@subsql)
set @totalPage=@@ROWCOUNT
set @PageCount=CEILING(@totalPage/@PageSize)
commit tran
end try
BEGIN CATCH
select
ERROR_NUMBER() ‘错误编号’,
ERROR_MESSAGE() ‘错误消息’,
ERROR_SEVERITY() ‘严重性’,
ERROR_STATE() ‘状态号’,
ERROR_LINE() ‘错误行号’,
ERROR_PROCEDURE() ‘错误对象(存储过程或触发器)名称’;
ROLLBACK TRAN
END CATCH
end
exec p_GetStudentList ”,”,’宗鸣放’,1,2,0
———————————–查询学校名称——————————-
create proc p_SchoolList
as
begin
select * from School
end
———————————-查询班级名称——————————-
create proc p_ClassList
(
@SchoolCode int
)
as
begin
select * from Class where SchoolCode=@SchoolCode
end
exec p_ClassList 1
———————————-添加学生信息——————————–
create proc p_AddStudent
(
@ClassCode int,
@StudentName varchar(50),
@Sex int
)
as
begin
insert into Student(ClassCode,StudentName,Sex) values(@ClassCode,@StudentName,@Sex)
select @@IDENTITY
return @@rowcount
end

exec p_AddStudent 1,’ss’,1
———————————-删除学生信息————————————-
create proc DeleteStudent
(
@StudentId int
)
as
begin
delete from Student where StudentId=@StudentId
return @@rowcount
end
———————————-修改学生信息———————————-
create proc p_UpdateStudent
(
@StudentId int,
@ClassCode int,
@StudentName varchar(50),
@Sex int
)
as
begin
update Student set ClassCode=@ClassCode,StudentName=@StudentName,Sex=@Sex where StudentId=@StudentId
return @@rowcount

end

alter proc p_GetStudentById
(
@StudentId int
)
as
begin
select a.StudentId,a.StudentName,a.Sex,b.ClassCode,b.ClassName,c.SchoolCode,c.SchoolName,
ROW_NUMBER() over(order by StudentId) as rownumber from Student as a
inner join Class as b on a.ClassCode=b.ClassCode
inner join School as c on b.SchoolCode=c.SchoolCode where StudentId=@StudentId
end