SQLServer------預存程序的使用

標籤：order   insert   asc   begin   arch   table   tween   rom   建立   

轉載：

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

 例子：

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 < 100begininsert 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 ‘陳老師‘, 1insert into Teacher select ‘李老師‘, 3insert into Teacher select ‘王老師‘, 2insert 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------預存程序的使用