SQLServer------存储过程的使用

 例子：

1.学生表

CREATE TABLE [dbo].[Student](
    [StudentID] [int] IDENTITY(1,1) NOT NULL,  --主键
    [Number] [varchar](15) NULL,   --学号
    [Name] [nchar](8) NULL,  --学生姓名
    [ClassID] [int] NOT NULL  --学生所在班级ID
)

插入学生数据：
declare @count int =1;
while @count < 100
begin
insert into Student select @count,‘学生‘+ convert(varchar,@count,15),cast(ceiling(rand() * 5) as int)
set @count = @count + 1; 
end

2.教师表

create table Teacher(
    [TeacherID] [int] IDENTITY(1,1) NOT NULL, --老师ID
    [TeacherName] [nchar](8) NULL,  --老师名称
    [ClassID] [int] NOT NULL  -- 老师所教的班级ID
)

插入数据：
insert into Teacher select ‘陈老师‘, 1
insert into Teacher select ‘李老师‘, 3
insert into Teacher select ‘王老师‘, 2
insert into Teacher select ‘赵老师‘, 5

3.班级表

create table Class(
    [ClassID] [int] IDENTITY(1,1) NOT NULL,   --班级ID
    [Code] [varchar](3) NULL,   --班级编号
    [ClassName] [nchar](8) NULL  --班级名
)

插入班级数据：
insert into Class select ‘003‘,‘计算机3班‘
insert into Class select ‘001‘,‘计算机1班‘
insert into Class select ‘002‘,‘计算机2班‘
insert into Class select ‘005‘,‘计算机5班‘
insert into Class select ‘004‘,‘计算机4班‘

4.创建存储过程

create proc proc_getStudentRecord(
    @pageIndex int,         --页码
    @pageSize int,          --每页信息数
    @name nchar(8) output   --任课老师
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize + 1
    set @endRow = @startRow + @pageSize -1
    
    select s.Number,s.Name,b.Code,b.ClassName from(
        select *, row_number() over (order by StudentID asc) as num from Student a where exists(select 1 from Teacher t where a.ClassID = t.ClassID and t.TeacherName = @name)
    ) s 
    join
    Class as b on
    b.ClassID = s.ClassID
    where s.num between @startRow and @endRow;
    
go

4.执行存储过程

exec proc_getStudentRecord 1,5,‘陈老师‘

SQLServer------存储过程的使用

标签：order   insert   asc   begin   arch   table   tween   rom   创建