sqlserver存储过程详细代码超赞.docxVIP

use jxgl--首先判断有没有已经建立up_getallstudents存储过程，有则先删除if exists (select name from sysobjects where name = up_getallstudents and type = p ) drop procedure up_getallstudents--编写存储过程up_getallstudents,用于获取学生表students的所有记录create procedure up_getallstudentsasselect * from students--使用execute执行存储过程up_getallstudentsexec up_getallstudents--也可写成execute up_getallstudents--编写一个存储过程up_insertstudent,完成学生表students数据的插入--1、不带默认值的参数create procedure up_insertstudent@sid varchar(15), @sname varchar(30), @ssex char(10),@sbirth datetime, @sbirthplace varchar(300)asbegininsert into students(stu_id, stu_name, stu_sex, stu_birth, stu_birthplace)values(@sid, @sname, @ssex, @sbirth, @sbirthplace)endexec up_insertstudent 200712110111, 肖玉峰, 男, 1975-02-05, 山东省滕州市木石镇--等同于exec up_insertstudent@sname = 肖玉峰, @sid = 200712110111, @ssex = 男, @sbirth = 1975-02-05, @sbirthplace = 山东省滕州市木石镇drop procedure up_insertstudentdelete students where stu_name = 肖玉峰insert into students(stu_id, stu_name, stu_sex, stu_birth, stu_birthplace)values(200712110110, 马缪, 男, 1986-010-17 00:00:00.000, 广东广州)--编写一个存储过程up_delstudentbyname,根据输入的学生姓名，删除该学生记录if object_id(up_delstudentbyname, p) is not nulldrop procedure up_delstudentbynamegocreate procedure up_delstudentbyname@sname varchar(30) asbegindelete from studentswhere stu_name = @snameend--调用存储过程的代码如下：exec up_delstudentbyname 马缪 select * from students--编写一个存储过程up_getstuinformationbyname,根据输入的学生姓名，显示该学生的学号、姓名、课程名和成绩if object_id(up_getstuinformationbyname, p) is not nulldrop procedure up_getstuinformationbynamegocreate procedure up_getstuinformationbyname@sname varchar(30)as beginselect S.stu_id, stu_name, cour_name, score from students S, courses C, course_score CSwhere S.stu_id = CS.stu_id and CS.cour_id = C.cour_id and stu_name = @snameend--调用存储过程的代码如下：exec up_getstuinformationbyname 仇立权 --ok--2、带默认值的参数--编写一个存储过程up_insertstuwithdefault，给参数定义默认值，完成学生表students数据的插入drop procedure up_insertstuwithdefaultgocreate procedure up_insertstuwithdefault@sid varchar(15), @sname varchar(3