院校搜索存储过程

ALTER PROCEDURE [dbo].[Sp_SearchSchool]
 -- Add the parameters for the stored procedure here
@countryid int=0,
@resumeid int=0,
@paytype int=0
AS
declare @sql nvarchar(4000)
declare @cons varchar(200)
set @cons=''
if(@resumeid>0)
begin
set @cons=@cons+' and resumeid='+cast(@resumeid as varchar(20))
end
if(@paytype>0)
begin
set @cons=@cons+' and paytype='+cast(@paytype as varchar(20))
end
set @sql='select a.*,b.* from dbo.T_UniversityInfo a inner join  (select count(id) as countnum,unid from T_SchoolSpec where 1=1'+@cons+' group by unid) b

on a.id=b.unid'

exec(@sql)