具体调用实例见代码最后一行注释区域
--if exists(select * from sysobjects where id = object_id(N'page_search') and type = 'P')
--drop PROCEDURE [dbo].[page_search]
--go
CREATE PROCEDURE [dbo].[page_search2](
@strTable varchar(50), --要查询的表
@strColumn varchar(500), --要查询的字段（*表示全部字段）
@left_join_table varchar(500), --多表联查的表名,多个表之间用,隔开
@on_ori nvarchar(1000), --多表联查的条件,源表@strTable字段
@on_goal nvarchar(1000), --多表联查的条件,目标表@left_join_table对应字段,多个字段之间用,隔开
@and_search nvarchar(1000), --搜索条件,多个字段之间用,隔开
@calc varchar(1000), --运算符
@and_search_value nvarchar(1000), --搜索条件的值,多个值之间用,隔开
@startindex varchar(10)=0, --起始索引
@perPage varchar(10) = 10 --每页条数
)
WITH ENCRYPTION
AS
BEGIN SET NOCOUNT ON;
--变量
declare @sqlString nvarchar(4000)=''; --完整的select语句
declare @sql_1 varchar(500)=''; --sql条件1
declare @sql_2 varchar(500)=''; --sql条件2 连表查询条件
declare @sql_3 varchar(500)=''; --sql条件3 搜索条件
declare @sqlString2 nvarchar(4000)=''; --完整的select语句
declare @curr_table int; --表--字符串的当前位置
declare @curr_field int; --字段--字符串的当前位置
declare @curr_search_k int; --表--字符串的当前位置
declare @curr_search_v int; --字段--字符串的当前位置
declare @curr_calc int; --运算符--字符串的当前位置
declare @num1 int; --连表查询表的个数
declare @num2 int; --搜索条件个数
declare @prev1 int; --字段--字符串的当前位置
declare @prev2 int; --字段--字符串的当前位置
declare @prev3 int; --字段--字符串的当前位置
declare @prev4 int; --字段--字符串的当前位置
declare @prev5 int; --字段--字符串的当前位置
declare @res varchar(20);
declare @rrr varchar(200);
--变量赋初值
set @num1=(len(@left_join_table)-len(replace(@left_join_table,',','')))+1; --print @num1; --3
set @num2=(len(@and_search)-len(replace(@and_search,',','')))+1; --print @num2; --6
set @prev1=1;
set @prev2=1;
set @prev3=1;
set @prev4=1;
set @prev5=1;
set @sql_1='SELECT rowNum =ROW_NUMBER() over (order by '+@strTable+'.'+@on_ori+'),'+ @strColumn+' FROM ' +@strTable;
--开始循环处理--处理连表查询部分
while ( @num1 > 0)
begin
set @curr_table=charindex(',',@left_join_table,@prev1); --print @curr_table -- 20 39 0
set @curr_field= charindex(',',@on_goal,@prev2); --print @curr_field -- 3 12 0
if @num1>1
begin
set @sql_2 =' left join '+substring(@left_join_table,@prev1,@curr_table-@prev1)+' on '+@strTable+'.'+@on_ori+'='+substring(@left_join_table,@prev1,@curr_table-@prev1)+'.'+substring(@on_goal,@prev2,@curr_field-@prev2)+''+@sql_2;
end
else--最后一个
begin
set @sql_2 =@sql_2 + ' left join '+substring(@left_join_table,@prev1,len(@left_join_table)-@prev1+1)+' on '+@strTable+'.'+@on_ori+'='+substring(@left_join_table,@prev1,len(@left_join_table)-@prev1+1)+'.'+substring(@on_goal,@prev2,len(@on_goal)-@prev2+1);
break;
end
set @num1=@num1-1;
set @prev1=@curr_table+1;
set @prev2=@curr_field+1;
end
--开始循环处理--处理搜索条件部分
while ( @num2 > 0)
begin
set @curr_search_k= charindex(',',@and_search,@prev3);
--print @curr_search_k -- 8 17 21 34 40 0 set @curr_search_v= charindex(',',@and_search_value,@prev4);
--print @curr_search_v -- 12 17 22 27 32 0 set @curr_calc= charindex(',',@calc,@prev5);
--print @curr_search_v -- 12 17 22 27 32 0 if @num2>1
begin
set @res=substring(@calc,@prev5,@curr_calc-@prev5);
set @rrr=substring(@and_search_value,@prev4,@curr_search_v-@prev4);
print @rrr
if ( @res = 'LIKE' )
set @sql_3 = ' and ('+substring(@and_search_value,@prev4,@curr_search_v-@prev4)+' IS NULL OR '+substring(@and_search,@prev3,@curr_search_k-@prev3)+' LIKE '+@rrr+')'+''+@sql_3;
else
set @sql_3 = ' and ('+substring(@and_search_value,@prev4,@curr_search_v-@prev4)+' IS NULL OR '+substring(@and_search,@prev3,@curr_search_k-@prev3)+' '+substring(@calc,@prev5,@curr_calc-@prev5)+' '+substring(@and_search_value,@prev4,@curr_search_v-@prev4)+')'+''+@sql_3;
end
else--最后一个
begin
set @res=substring(@calc,@prev5,len(@calc)-@prev5+1);
set @rrr=substring(@and_search_value,@prev4,len(@and_search_value)-@prev4+1);
if ( @res = 'LIKE' )
begin
set @sql_3 =@sql_3 + ' and ('+substring(@and_search_value,@prev4,len(@and_search_value)-@prev4+1)+' IS NULL OR '+substring(@and_search,@prev3,len(@and_search)-@prev3+1)+' LIKE '+@rrr+')';
break;
end
else
begin
set @sql_3 =@sql_3 + ' and ('+substring(@and_search_value,@prev4,len(@and_search_value)-@prev4+1)+' IS NULL OR '+substring(@and_search,@prev3,len(@and_search)-@prev3+1)+' '+substring(@calc,@prev5,len(@calc)-@prev5+1)+' '+substring(@and_search_value,@prev4,len(@and_search_value)-@prev4+1)+')';
break;
end
end
set @num2=@num2-1;
set @prev3=@curr_search_k+1;
set @prev4=@curr_search_v+1;
set @prev5=@curr_calc+1;
end
set @sqlString=@sql_1+@sql_2+' where 1=1 '+@sql_3
set @sqlString2='select top('+@perPage+') rowNum,totalNum = (select count(0) from ('+@sqlString+') as cte),pageCount=ceiling((((select count(0) from ('+@sqlString+') as cte )+0.0))/CAST('+@perPage+' as varchar)),'+@strColumn+' from ('+@sqlString+') as cte where rowNum > '+@startindex+' order by rowNum ASC';
PRINT @sqlString
exec(@sqlString2)
END
--execute [dbo].[page_search2] "base._Member",'AcctNbr,AcctName,ssn,Since,DiffEligible,DiffCardOn,CurrSelfRank','base._MemberProfile,tree._Node,tree._Leaf','ID','ID,MemberID,MemberID','AcctNbr,AcctName,SSN,CurrSelfRank,Since,Since',"=,LIKE,=,=,>,<","null,'zouke%',null,null,null,null","1","10"
if exists(select * from sysobjects where id = object_id(N'page_search') and type = 'P')
drop PROCEDURE [dbo].[page_search]
go
CREATE PROCEDURE [dbo].[page_search](
@startindex int=0,
@perPage int = 10,
@acctNbr varchar(100)=null,
@user_name nvarchar(100)=null,
@ssn varchar(100)=null,
@rank varchar(100)=null,
@from_since varchar(100)=null,
@to_since varchar(100)=null
)
WITH ENCRYPTION
AS
BEGIN SET NOCOUNT ON;
with cte as(
select
rowNum = ROW_NUMBER() over (order by base._Member.ID),
AcctNbr,
AcctName,
ssn,
tree._Leaf.CountryID,
Since,
DiffEligible,
DiffCardOn,
CurrSelfRank
from base._Member
left join base._MemberProfile on base._Member.ID=base._MemberProfile.ID
left join tree._Node on base._Member.ID= tree._Node.MemberID
left join tree._Leaf on base._Member.ID= tree._Leaf.MemberID
where 1=1
and (@acctNbr IS NULL OR AcctNbr = @acctNbr)
--and (@user_name IS NULL OR AcctName = @user_name)
and (@user_name IS NULL OR AcctName LIKE @user_name + '%')
and (@ssn IS NULL OR SSN = @ssn)
and (@rank IS NULL OR CurrSelfRank = @rank)
and (@from_since IS NULL OR Since > @from_since)
and (@to_since IS NULL OR Since < @to_since)
)
select top(@perPage)
rowNum,
totalNum = (select count(0) from cte),
pageCount=ceiling((((select count(0) from cte)+0.0))/CAST(@perPage as varchar)),
AcctNbr,
AcctName,
ssn,
CountryID,
Since,DiffEligible,
DiffCardOn,
CurrSelfRank
from cte
where rowNum > @startindex
order by rowNum ASC
END
--execute [dbo].[GetMemberInfo3] 0,1000,null,null,null,null,'2012-05-16','2013-05-16'