http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html

CREATE TABLE [dbo].[Student](

    [StudentID] [int] IDENTITY(,) NOT NULL,  --主键

    [Number] [varchar]() NULL,   --学号

    [Name] [nchar]() NULL,  --学生姓名

    [ClassID] [int] NOT NULL  --学生所在班级ID

)

插入学生数据：

declare @count int =;

while @count <

begin

insert into Student select @count,'学生'+ convert(varchar,@count,),cast(ceiling(rand() * ) as int)

set @count = @count + ;

end

create table Teacher(

    [TeacherID] [int] IDENTITY(,) NOT NULL, --老师ID

    [TeacherName] [nchar]() NULL,  --老师名称

    [ClassID] [int] NOT NULL  -- 老师所教的班级ID

)

插入数据：

insert into Teacher select '陈老师',

insert into Teacher select '李老师',

insert into Teacher select '王老师',

insert into Teacher select '赵老师', 

create table Class(

    [ClassID] [int] IDENTITY(,) NOT NULL,   --班级ID

    [Code] [varchar]() NULL,   --班级编号

    [ClassName] [nchar]() NULL  --班级名

)

插入班级数据：

insert into Class select '','计算机3班'

insert into Class select '','计算机1班'

insert into Class select '','计算机2班'

insert into Class select '','计算机5班'

insert into Class select '','计算机4班'

create proc proc_getStudentRecord(

    @pageIndex int,         --页码

    @pageSize int,          --每页信息数

    @name nchar() output   --任课老师

)

as

    declare @startRow int, @endRow int

    set @startRow = (@pageIndex - ) * @pageSize +

    set @endRow = @startRow + @pageSize -

    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  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

exec proc_getStudentRecord ,,'陈老师'