sql server存储过程

创建表的语句:

create table student(sno int primary key,sname nvarchar(30),sgentle nvarchar(2),sage int,sbirth smalldatetime,sdept nvarchar(30))--drop table student;create table sc(sno int foreign key references student(sno),cno int,grade int)--drop table sc;create table course(cno int,cname nvarchar(50),cgrade int,--constraint PK_CNO foreign key(cno) references sc(cno))--drop table course;

插入数据：

select * from student;insert into student(sno,sname,sgentle,sage,sbirth,sdept) values(990001, '张三', '男', 20, '1987-8-4', '计算机');insert into student(sno,sname,sgentle,sage,sbirth,sdept) values(990002, '陈林', '女', 19, '1988-5-21', '外语');insert into student(sno,sname,sgentle,sage,sbirth,sdept) values(990003, '吴中', '男', 21, '1986-4-12', '工商管理');insert into student(sno,sname,sgentle,sage,sbirth,sdept) values(990005, '王冰', '女', 20, '1987-2-16', '艺术');insert into student(sno,sname,sgentle,sage,sbirth,sdept) values(990012, '张中和', '男', 22, '1985-8-28', '艺术');insert into student(sno,sname,sgentle,sage,sbirth,sdept) values(990026, '陈维佳', '男', 21, '1986-7-1', '计算机');insert into student(sno,sname,sgentle,sage,sbirth,sdept) values(990028, '丽莎', '女', 21, '1986-10-21', '计算机');insert into sc(sno,cno,grade) values(990001,003,85);insert into sc(sno,cno,grade) values(990001,004,78);insert into sc(sno,cno,grade) values(990003,001,95);insert into sc(sno,cno,grade) values(990012,004,62);insert into sc(sno,cno,grade) values(990012,006,74);insert into sc(sno,cno,grade) values(990012,007,81);insert into sc(sno,cno) values(990026,001);insert into sc(sno,cno,grade) values(990026,003,77);insert into sc(sno,cno) values(990028,006);insert into course(cno,cname,cgrade) values(001,'计算机基础',2);insert into course(cno,cname,cgrade) values(003,'数据结构',4);insert into course(cno,cname,cgrade) values(004,'操作系统',4);insert into course(cno,cname,cgrade) values(006,'数据库原理',4);insert into course(cno,cname,cgrade) values(007,'软件工程',4);select * from course;select * from sc;

存储过程的语句：

--创建存储过程create procedure stu_allasselect * from student;exec stu_all;--执行存储过程exec sp_helptext stu_all;--调用系统存储过程--查询选修了数据结构的学生的信息create procedure stu_dsasselect * from student where sno in (select sno from sc where cno = (select cno from course where cname = '数据结构'))exec stu_ds;--带参数的存储过程 --创建一个存储过程stu_sno，根据用户执行的学号查询学生信息create procedure stu_sno@sno varchar(10)asselect * from student where sno = @sno;exec stu_sno '990001';--带参存储过程的使用--带两个参数的存储过程create procedure stu_grade@sdept varchar(50),@cname varchar(10)asselect student.sno 学号,student.sname 姓名,student.sgentle 性别,course.cname 课程名, course.cgrade 学分, sc.grade 成绩from studentjoin scon student.sno = sc.snojoin courseon course.cno = sc.cnowhere student.sdept = @sdept and course.cname = @cnamedrop procedure stu_grade;exec stu_grade '计算机','数据结构';--带通配符的存储过程create procedure stu_sname@sname varchar(10)asselect * from student where sname like @snameexec stu_sname '张%';