SQL Server------The use of stored procedures

Reproduced:

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

Example:

1. Student Table

CREATE TABLE [dbo]. [Student] ([StudentID] [int] IDENTITY (1,1) Not NULL,--primary key [number] [varchar] ( the) NULL,--study number [Name] [nchar] (8) NULL,--student name [ClassID] [int] Not NULL--student's class ID) Insert student data: Declare @countint=1; while@count < -Begininsert into StudentSelect@count,'Student'+ CONVERT (varchar, @count, the), CAST (Ceiling (rand () *5) as int)Set@count = @count +1; end

2. Teachers ' tables

CREATE Table Teacher ([Teacherid] [int] IDENTITY (1,1) Not NULL,--teacher ID [teachername] [nchar] (8) NULL,--Teacher's name [ClassID] [int] Not NULL--Teacher-taught class ID) Insert data: INSERT INTO TeacherSelect 'Miss Chen',1INSERT INTO TeacherSelect 'teacher Li',3INSERT INTO TeacherSelect 'Miss Wang',2INSERT INTO TeacherSelect 'Miss Zhao',5

3. Class table

CREATE Table Class ([ClassID] [int] IDENTITY (1,1) Not NULL,--class ID [Code] [varchar] (3) NULL,--class Number [ClassName] [nchar] (8) NULL--class name) Insert class data: INSERT INTO ClassSelect '003','Computer Class 3'INSERT INTO ClassSelect '001','Computer Class 1'INSERT INTO ClassSelect '002','Computer Class 2'INSERT INTO ClassSelect '005','Computer Class 5'INSERT INTO ClassSelect '004','Computer Class 4'

4. Create a stored procedure

create proc Proc_getstudentrecord (@pageIndexint,         --page @pageSizeint,          --number of messages per page @name nchar (8) Output--classroom teacher) asDeclare @startRowint, @endRowint    Set@startRow = (@pageIndex-1) * @pageSize +1    Set@endRow = @startRow + @pageSize-1        SelectS.number,s.name,b.code,b.classname from(        Select*, Row_number () over (order by studentid ASC) asNum fromStudent AwhereExistsSelect 1  fromTeacher TwhereA.classid = T.classid and T.teachername =@name)) s join Class asB on B.classid=S.classidwheres.num between @startRow and @endRow; Go

4. Execute the Stored procedure

1,5,' Miss Chen '

SQL Server------The use of stored procedures