分页且带条件的存储过程

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