转载:
http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html
例子:
1.学生表
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
2.教师表
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 '赵老师',
3.班级表
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班'
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
4.执行存储过程
exec proc_getStudentRecord ,,'陈老师'