创建
create procedure getPractice
@name varchar(24)
as
if (select count(*) from D_Info where D_Name=@name) <> 0
begin
select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
from st_info,s_c_info,C_Info
where left(st_info.St_ID,2) = (select D_ID
from D_Info
where D_Name=@name)
and s_c_info.st_id=st_info.St_ID
and s_c_info.c_no=C_Info.C_No
and C_Info.C_Type='实践'
end
else
print('院系不存在')
go
调试
exec getPractice '法学院'
exec getPractice '材料科学与工程学院'
改名
sp_rename 'getPractice','getPctStu'
修改
alter procedure getPctStu
@name varchar(24)
as
if (select count(*) from D_Info where D_Name=@name) <> 0
begin
select count(*)
from st_info,s_c_info,C_Info
where left(st_info.St_ID,2) = (select D_ID
from D_Info
where D_Name=@name)
and s_c_info.st_id=st_info.St_ID
and s_c_info.c_no=C_Info.C_No
and C_Info.C_Type='实践'
end
else
print('院系不存在')
go
调试2
exec getPctStu '法学院'
exec getPctStu '法学院啊'
再改
alter procedure getPctStu
@name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8
as
if (select count(*) from D_Info where D_Name=@name) <> 0
begin
-- distinct st_info.St_ID
select count(distinct st_info.St_ID)
from st_info,s_c_info,C_Info
where left(st_info.St_ID,2) = (select D_ID
from D_Info
where D_Name=@name)
and s_c_info.st_id=st_info.St_ID
and s_c_info.c_no=C_Info.C_No
and C_Info.C_Type='实践'
end
else
print('院系不存在')
go
调试3
exec getPctStu '法学院'
exec getPctStu '法学院啊'