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_all
as
select * from student;

exec stu_all;--执行存储过程
exec sp_helptext stu_all;--调用系统存储过程

--查询选修了数据结构的学生的信息
create procedure stu_ds
as
select * 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)
as
select * from student where sno = @sno;

exec stu_sno '990001';--带参存储过程的使用


--带两个参数的存储过程
create procedure stu_grade
@sdept varchar(50),
@cname varchar(10)
as
select student.sno 学号,student.sname 姓名,student.sgentle 性别,course.cname 课程名, course.cgrade 学分, sc.grade 成绩
from student
join sc
on student.sno = sc.sno
join course
on course.cno = sc.cno
where student.sdept = @sdept and course.cname = @cname

drop procedure stu_grade;

exec stu_grade '计算机','数据结构';

--带通配符的存储过程
create procedure stu_sname
@sname varchar(10)
as
select * from student where sname like @sname

exec stu_sname '张%';