自用自用存储过程

创建

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 '法学院啊'