set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROC [dbo].[user_index]
(
@PageIndex int,
@PageSize int,
@education_id nvarchar(200)=null,
@city_id nvarchar(200)=null,
@language_id nvarchar(200)=null,
@key nvarchar(200)=null,
@sex nvarchar(200)=null,
@height1 nvarchar(200)=null,
@height2 nvarchar(200)=null,
@age1 nvarchar(200)=null,
@age2 nvarchar(200)=null
)
as
create table #table
(id int,name nvarchar(100),education nvarchar(100),city nvarchar(100),sex nvarchar(100),age nvarchar(100),height nvarchar(100),
uid uniqueidentifier)
declare @Where nvarchar(4000)
set @Where=''
IF @education_id IS NOT null AND @education_id <>''
BEGIN
select @Where=@Where + ' AND general_education.general_education_id >= '+@education_id+''
END
IF @city_id IS NOT null AND @city_id <>''
BEGIN
select @Where=@Where + ' AND city.place_id = '+@city_id+''
END
IF @language_id IS NOT null AND @language_id <>''
BEGIN
select @Where=@Where + ' AND lang.language_id = '+@language_id+''
END
IF @sex IS NOT null AND @sex <>''
BEGIN
set @Where=@Where+' AND sex = '''+@sex+''''
END
IF @height1 IS NOT null AND @height1 <>'' and @height2 IS NOT null AND @height2 <>''
BEGIN
select @Where=@Where + ' and (height between '+@height1+' and '+@height2+')'
END
IF @age1 IS NOT null AND @age1 <>'' and @age2 IS NOT null AND @age2 <>''
BEGIN
select @Where=@Where + ' and (datediff(year,birthday,getdate()) between '+@age1+' and '+@age2+')'
END
IF @key IS NOT null AND @key <>''
BEGIN
set @Where=@Where + ' and user_work_exp.work_exp_description LIKE ''%'+@key+'%'''
END
print @where
declare @sql nvarchar(4000)
set @sql='insert into #table select distinct user_profile_id,name,general_education.generl_education_desc,city.reg_name,sex,
datediff(year,birthday,getdate())as age,height,user_profile.uid
from user_profile left join general_education on user_profile.general_education_id=general_education.general_education_id
left join user_work_exp on user_work_exp.uid=user_profile.uid left join
(select location_id,location.place_id,reg_name from location left join location_place on location.place_id=location_place.place_id)as city
on city.location_id=user_profile.location_id left join
(select user_language_skill.language_id,user_language_skill.uid,user_language_type.language_name from
user_language_skill left join user_language_type on user_language_skill.language_id=user_language_type.language_id) as lang
on lang.uid=user_profile.uid where 1=1 '+ @Where
print @sql
exec(@sql)
if(@PageIndex=1)
begin
select top (@PageSize) * from #table
end
else
begin
select top (@PageSize) * from #table where id not in(select top(@PageSize*(@PageIndex-1)) id from #table)
end