11-29-SQL

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