USE xscj
GO
CREATE RULE xs_rule
AS
@学分>=0 AND @学分<=6
GO
EXEC sp_bindrule 'xs_rule','kc.学分'
/*无参数的存储过程*/
/*1.创建存储过程，能查询xsqk表中所有学生信息*/
CREATE proc p_xsqk
as select * from xsqk/*存储*/
exec p_xsqk/*调用*/
/*带输入参数的存储过程*/
/*2.创建存储过程，查询某学号的学生信息*/
CREATE proc p_xsqk1
@xh char(10)
as
select * from xsqk
where 学号=@xh
p_xsqk1 "2012130101"
/*3.创建存储过程，能查询表xsqk中某一学号范围内的学生信息*/
CREATE proc p_xsqk2
@xh1 char(10),@xh2 char(10)
as
select *
from xsqk
where 学号 between @xh1 and @xh2
p_xsqk2 "2012130101","2012130105"
/*课本P198例9-1*/
create procedure p_KC
as
select * from kc
where 开课学期=1
go
exec p_KC
/*课本P199例9-2*/
create procedure p_CJ
@kch char(3)
as
select 学号,课程号,成绩 from xs_kc
where 课程号=@kch
go
exec p_CJ '102'
/*课本P199例9-3*/
create procedure p_KCH
@skjs varchar(20),@kch char(3) output
as
select @kch=课程号 from kc
where 授课教师=@skjs
go
declare @skjs varchar(20),@kch char(3)
set @skjs='王颐'
exec p_KCH @skjs,@kch output
print @skjs+'教师所授课程的课程号为'+@kch
