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_getallstudents as select * from students --使用execute执行存储过程up_getallstudents exec 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) as begin insert into students (stu_id, stu_name, stu_sex, stu_birth, stu_birthplace) values (@sid, @sname, @ssex, @sbirth, @sbirthplace) end exec up_insertstudent 200712110111, 肖玉峰, 男, 1975-02-05, 山东省滕州市木石镇 --等同于 exec up_insertstudent @sname = 肖玉峰, @sid = 200712110111, @ssex = 男, @sbirth = 1975-02-05, @sbirthplace = 山东省滕州市木石镇 drop procedure up_insertstudent delete 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 null drop procedure up_delstudentbyname go create procedure up_delstudentbyname @sname varchar(30) as begin delete from students where stu_name = @sname end --调用存储过程的代码如下： exec up_delstudentbyname 马缪 select * from students --编写一个存储过程up_getstuinformationbyname,根据输入的学生姓名，显示该学生的学号、姓名、课程名和成绩 if object_id(up_getstuinformationbyname, p) is not null drop procedure up_getstuinformationbyname go create procedure up_getstuinformationbyname @sname varchar(30) as begin select S.stu_id, stu_name, cour_name, score from students S, courses C, course_score CS where S.stu_id = CS.stu_id and CS.cour_id = C.cour_id and stu_name = @sname end --调用存储过程的代码如下： exec up_getstuinformationbyname 仇立权 --ok --2、带默认值的参数 --编写一个存储过程up_insertstuwithdefault，给参数定义默认值，完成学生表students数据的插入 drop procedure up_insertstuwithde