模糊匹配查询用存储过程实现

use studentmanage;
drop procedure p_get_grade;
create procedure p_get_grade @studentname varchar(50),@classname varchar(10)
as
begin
declare  @sql nvarchar(1000);

set @sql=' select s.name,c.name,g.grade from t_class c ,t_grade g,t_student s where  c.id=g.c_id and s.id=g.s_id ';
if @studentname is not null
set @sql=@sql+' and s.name like ''%'+@studentname+'%''';
if @classname is not null
set @sql=@sql+ ' and c.name like ''%'+@classname+'%''';
print @sql;
exec  sp_executesql @sql

--exec  "select s.name,c.name,g.grade from t_class c ,t_grade g,t_student s where  c.id=g.c_id and s.id=g.s_id"
end
go
exec  p_get_grade @studentname='qiao',@classname ='math'
exec  p_get_grade @studentname='qiao',@classname =''
exec  p_get_grade @studentname='',@classname =''
go