具体调用实例见代码最后一行注释区域
--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'