create procedure sp(parameter type,......) as begin @str='select distinct top '+@Pagenum+' a.name,pic,a.id,address,star,a.price,cityid,roomNum,stayTime,feature,state from hotel a,room b where a.id=b.hid and ('''+@name+'''='''' or a.name like ''%'+left(@name,@c-@y)+'%'') and ('''+@tel+'''='''' or paytel like ''%'+@tel+'%'') and ('''+@roomName+'''='''' or b.name like ''%'+@roomName+'%'') and ('''+@cityid+'''='''' or cityid like '''+@cityid+'%'') and ('''+@star+'''='''' or star = '''+@star+''') and ('''+@state+'''='''' or state = '''+@state+''') and ('''+@price+'''='''' or a.price = '''+@price+''') and ('''+@address+'''='''' or address like ''%'+left(@address,@c-@y)+'%'') and ('''+@roomNum +'''=''0'' or roomNum >='+@roomNum+') and ('''+@roNum+'''=''0'' or b.num >='+@roNum+') and ('''+@feature+'''='''' or cityid like ''%'+replace(@feature,'_','%')+'%'')' end
as
begin
@str='select distinct top '+@Pagenum+' a.name,pic,a.id,address,star,a.price,cityid,roomNum,stayTime,feature,state from hotel a,room b where a.id=b.hid
and ('''+@name+'''='''' or a.name like ''%'+left(@name,@c-@y)+'%'')
and ('''+@tel+'''='''' or paytel like ''%'+@tel+'%'')
and ('''+@roomName+'''='''' or b.name like ''%'+@roomName+'%'')
and ('''+@cityid+'''='''' or cityid like '''+@cityid+'%'')
and ('''+@star+'''='''' or star = '''+@star+''')
and ('''+@state+'''='''' or state = '''+@state+''')
and ('''+@price+'''='''' or a.price = '''+@price+''')
and ('''+@address+'''='''' or address like ''%'+left(@address,@c-@y)+'%'')
and ('''+@roomNum +'''=''0'' or roomNum >='+@roomNum+')
and ('''+@roNum+'''=''0'' or b.num >='+@roNum+')
and ('''+@feature+'''='''' or cityid like ''%'+replace(@feature,'_','%')+'%'')'
end