数据查询存储过程-XS.doc

课程信息：根据课程编号查询课程名，课程类型，课程学分，课程学时。 create proc p_select_c_by_id @c_id varchar(15) as if (select count(*) from c where c_id=@c_id)=0 begin print NO return 1 end else begin select c_name,c_type,c_mark,c_time from c where c_id=@c_id return 0 end 学生信息：根据学号查询姓名、年龄、专业，院系信息。 create proc p_select_s_by_id @s_id varchar(15) as if (select count(*) from s where s_id=@s_id)=0 begin print NO return 1 end else begin select s_name,(year(getdate())-year(s_birthday)) as 年龄,zy_name,yx_name from s,zy,yx where s_id=@s_id and s.yx_markid=yx.yx_markid and s.zy_markid=zy.zy_markid return 0 end 教师信息：根据工号查询姓名、职称，薪金信息。 create proc p_select_t_by_id @t_id varchar(10) as if (select count(*) from t where t_id=@t_id)=0 begin print NO return 1 end else begin select t_name,t_pro,t_salary from t where t_id=@t_id return 0 end 教室信息：根据教室编号查询容量 create proc p_select_r_by_id @r_id varchar(10) as if (select count(*) from r where r_id=@r_id)=0 begin print NO return 1 end else begin select r_size from r where r_id=@r_id return 0 end 专业代码：根据编号查询专业名称 create proc p_select_zy_by_id @zy_id varchar(10) as if (select count(*) from zy where zy_id=@zy_id)=0 begin print NO return 1 end else begin select zy_name from zy where zy_id=@zy_id return 0 end 教研室代码：根据编号查询教研室名称 create proc p_select_jys_by_id @jys_id varchar(10) as if (select count(*) from jys where jys_id=@jys_id)=0 begin print NO return 1 end else begin select jys_name from jys where jys_id=@jys_id return 0 end 院系代码：根据编号查询院系名称 create proc p_select_yx_by_id @yx_id varchar(10) as if (select count(*) from yx where yx_id=@yx_id)=0 begin print NO return 1 end else begin select yx_name from yx where yx_id=@yx_id return 0 end 学生成绩：根据学号和课程号查询成绩 create proc p_select_sc_by_sc_id @s_id int,@c_id int as if (select count(*) from s,c where s.s_id=@s_id and c.c_id=@c_id)=0 begin print NO return 1 end else begin select sc_cj from sc,s,c where s.s_id=@s_id and c.c_id=@c_id and sc.s_markid=s.s_markid and sc.c_markid=c.c_markid return 0 end 课程安排：根据教室编号查询教师名