我写的分页过程,哎。。。一个字,慢,代码长的乱!!!高手牺牲时间指点指点不。。。在网页里执行老超时!
--我把代码帖出来,如果真能解决速度问题或者有更好的解决方法,我愿加分!谢谢了!急啊!!
--查询配对信息
CREATE PROCEDURE sp_tbbinfo_with_Tb_Information_mates_Search_test
(
@Status int OUTPUT, --状态
@ErrMsg varchar(255) OUTPUT, --错误信息
@pageRows int, --每页条数
@rowscount int, --记录总数
@keyWord varchar(255) = NULL, --关键字
@Sales_buy_type int = 3, --主营方向类别
@sales_Key varchar(255) = NULL, --配对的销售关键字
@buy_Key varchar(255) = NULL, --配对的求购关键字
@currPage int, --当前页
@forwardPage int = 1, --上一个页
@issuedate int = 30 --商机发布/更新的时间
)AS
SET NOCOUNT ON
Declare @SQL varchar(3000) --存储语句
--Declare @order_by varchar(255) --排序
Declare @sales_buy_search varchar(1000) --配对关键字
Declare @sales_SQL varchar(500) --销售配对查询语句
Declare @buy_SQL varchar(500) --采购配对查询语句
Declare @keyword_SQL varchar(500) --关键字查询语句
Declare @date_SQL varchar(255) --商机发布/更新查询语句
Declare @order_by varchar(100)
--Declare @order_by_temp varchar(100)
Declare @SomeID int
Create table #temp (ciBid int,cvcBName varchar(255),cvcBdetails varchar(2000),ciBSalestype int,ciUserid int,cdtRecordTime datetime,cvcImagepath varchar(255))
Create table #temp_Rlt (ciBid int,cvcBName varchar(255),cvcBdetails varchar(2000),ciBSalestype int,ciUserid int,cdtRecordTime datetime,cvcImagepath varchar(255))
SET @Status=0
SET @ErrMsg=''
Set @sales_buy_search=''
Set @sales_SQL=''
Set @buy_SQL=''
Set @keyword_SQL=''
-- SET @SQL = 'Select top '+convert(varchar,@pageRows)+' TB.ciBId,TB.cvcBName,TB.cvcBDetails,TB.ciBSalestype,TB.ciUserId,TB.cdtRecordTime,TB.cvcImagepath from tbbinfo TB where ciStatus>=0'
SET @SQL = 'Select top '+convert(varchar,@rowscount)+' TB.ciBId,TB.cvcBName,TB.cvcBDetails,TB.ciBSalestype,TB.ciUserId,TB.cdtRecordTime,TB.cvcImagepath from tbbinfo TB where ciStatus>=0'
--Set @order_by = ' order by TB.cdtRecordTime DESC,TB.ciBid DESC,TB.ciStatus DESC,TB.ciLevel DESC'
Set @order_by = ' order by cdtRecordTime DESC,ciBid DESC,ciStatus DESC,ciLevel DESC'
--Set @order_by_temp = ' order by cdtRecordTime DESC,ciBid DESC,ciStatus DESC,ciLevel DESC'
Set @sales_buy_search=''
if @sales_Key is null Set @sales_Key=''
if @buy_Key is null Set @buy_Key=''
if @keyWord is null Set @keyWord=''
if @sales_Key<>'' Set @sales_SQL=' ('+dbo.split_info_mates_cvcBname(@sales_Key,',')+') or ('+dbo.split_info_mates_cvcBDetails(@sales_Key,',')+')'
if @buy_Key<>'' Set @buy_SQL=' ('+dbo.split_info_mates_cvcBname(@buy_Key,',')+') or ('+dbo.split_info_mates_cvcBDetails(@buy_Key,',')+')'
Set @date_SQL=
Case @issuedate
when 1 then
case
when DatePart(Hour,GetDate())<=11 then
' and datediff(hour,getdate(),cdtrecordtime)<=24'
else
' and datediff(hour,getdate(),cdtrecordtime)=0'
end
when 3 then ' and datediff(day,getdate(),cdtrecordtime)<=2'
when 7 then ' and datediff(week,getdate(),cdtrecordtime)=0'
when 15 then ' and datediff(week,getdate(),cdtrecordtime)<=1'
when 30 then ' and datediff(month,getdate(),cdtrecordtime)=0'
when 45 then ' and datediff(week,getdate(),cdtrecordtime)<=5'
when 60 then ' and datediff(month,getdate(),cdtrecordtime)<=1'
when 90 then ' and datediff(month,getdate(),cdtrecordtime)<=2'
else ' and datediff(month,getdate(),cdtrecordtime)=0'
END
--关键字
if @keyWord<>''
Set @keyword_SQL = @keyword_SQL+' TB.cvcBkeyword like ''%'+@keyWord+'%'''
--else
--Set @sales_buy_search = @sales_buy_search+' and ('
if @keyword_SQL is null Set @keyword_SQL=''
if @buy_SQL is null Set @buy_SQL=''
Set @sales_buy_search =
Case
when @Sales_buy_type=3 then
case when (@sales_SQL<>'' and @sales_SQL is not null) then ' (('+@sales_SQL+') and TB.ciBsalesType=1)' end+case when (@buy_SQL<>'' and @buy_SQL is not null) then ' or (('+@buy_SQL+') and TB.ciBsalesType=2)' end
--' and (('+dbo.f_split(@sales_Key,',','cvcBname')+') or ('+dbo.f_split(@sales_Key,',','cvcBDetails')+') or ('+dbo.f_split(@buy_Key,',','cvcBname')+') or ('+dbo.f_split(@buy_Key,',','cvcBDetails')+'))'
when @Sales_buy_type=2 then
case when (@buy_SQL<>'' and @buy_SQL is not null) then @buy_SQL end
--' and (('+dbo.f_split(@buy_Key,',','cvcBname')+') or ('+dbo.f_split(@buy_Key,',','cvcBDetails')+')) and TB.ciBsalesType=2'
when @Sales_buy_type=1 then
case when (@sales_SQL<>'' and @sales_SQL is not null) then @sales_SQL end
--' and (('+dbo.f_split(@sales_Key,',','cvcBname')+') or ('+dbo.f_split(@sales_Key,',','cvcBDetails')+')) and TB.ciBsalesType=1'
END
if (len(@sales_buy_search)=3) or (@sales_buy_search=' or ') or (@sales_buy_search is null)
Set @sales_buy_search = ''
if @keyword_SQL<>'' and @keyword_SQL is not null Begin
if @sales_buy_search<>'' and @sales_buy_search is not null
Set @SQL = @SQL+' and ('+@keyword_SQL+' or '+@sales_buy_search+')'
else
Set @SQL = @SQL+' and ('+@keyword_SQL+')'
END
ELSE
if @sales_buy_search<>'' and @sales_buy_search is not null
Set @SQL = @SQL+' and ('+@sales_buy_search+')'
if @Sales_buy_type=1
Set @SQL = @SQL+' and TB.ciBsalesType=1'
if @Sales_buy_type=2
Set @SQL = @SQL+' and TB.ciBsalesType=2'
Set @SQL = @SQL+@date_SQL+@order_by
insert into #Temp execute(@SQL)
if @currPage=1
--insert into #temp_Rlt
Set @SQL = 'select top '+cast(@pageRows as varchar)+' ciBid,cvcBName,cvcBdetails,ciBSalestype,ciUserid,cdtRecordTime,cvcImagepath from #temp '
ELSE Begin
--insert into #temp_Rlt
Set @SomeID=(@currPage-1)*@pageRows
Set @SQL = 'select top '+cast(@pageRows as varchar)+' ciBid,cvcBName,cvcBdetails,ciBSalestype,ciUserid,cdtRecordTime,cvcImagepath from #temp where cibid not in (select top '+cast(@SomeID as varchar)+' cibid from #temp) '
END
insert into #temp_Rlt execute(@SQL)
select A.ciBid,A.cvcBName,A.cvcBdetails,A.ciBSalestype,A.ciUserid,A.cdtRecordTime,A.cvcImagepath,B.cvcENDomain as Url from #temp_Rlt A Left Outer Join tbUserChRelation B on A.Ciuserid=B.CiUserID
--select A.ciBid,A.cvcBName,A.cvcBdetails,A.ciBSalestype,A.ciUserid,A.cdtRecordTime,A.cvcImagepath,B.cvcENDomain as Url from #temp_Rlt as A Left join tbUserChRelation as B on A.Ciuserid=B.CiUserID
Drop table #temp
Drop table #temp_Rlt
IF @@Error<>0 BEGIN
SET @Status=-1
SET @ErrMsg='数据库异常错误,请稍后再试'
RETURN
END
GO