62,050
社区成员
发帖
与我相关
我的任务
分享
create PROCEDURE [dbo].[db_consult_service_Pagination_2010_6_9]
(
@strGetFields nvarchar(1500) = '*', -- 需要返回的列
@orderstr nvarchar(500)='1', -- 排序的字段名
@joinField nvarchar(500)='', -- 链接的字段 字段条件表前缀一定要用T 如 (select c_name from db_sys_class where nsort='consult' and classid = T.classid) as classname
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@strWhere nvarchar(1500) = '' -- 查询条件 (注意: 不要加 where)
)
AS
declare @strSQL varchar(5000) -- 主语句
declare @selectField nvarchar(2000)
if @orderstr='' set @orderstr='updatetim desc'
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(1) as Total from dbo.db_consult_service where '+@strWhere
else
set @strSQL = 'select count(1) as Total from dbo.db_consult_service'
end
else
begin
if @joinField != ''
set @selectField = @strGetFields+', '+@joinField
else
set @selectField=@strGetFields
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@selectField+' from dbo.db_consult_service as T where ' + @strWhere + ' order by ' + @orderstr
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@selectField+' from dbo.db_consult_service as T order by '+ @orderstr
end
else
begin
if @strwhere=''
begin
SET @strSQL = 'SELECT TOP ' + str(@pageSize) +' '+@selectField+' FROM'
+'(SELECT '+@strGetFields+',ROW_NUMBER() OVER (order by '+@orderstr+') AS RowNo FROM db_consult_service) AS T '
+'WHERE RowNo >'+str((@pageIndex-1)*@pageSize)
end
else IF @strWhere != ''
begin
SET @strSQL = 'SELECT TOP ' + str(@pageSize) +' '+@selectField+' FROM'
+'(SELECT '+@strGetFields+',ROW_NUMBER() OVER (order by '+@orderstr+') AS RowNo FROM db_consult_service WHERE ' + @strWhere + ' ) AS T '
+'WHERE RowNo >'+str((@pageIndex-1)*@pageSize)
end
end
end
exec (@strSQL)
CREATE PROCEDURE sp_member_get_firend_list
@id bigint,
@pagesize int,
@pageindex int,
@docount bit,
@condition varchar(100)
AS
set nocount on
if(@docount=1)
exec(' SELECT count(*) from web_Clients a inner join web_Clients_Partners b on a.ClientID=PartnerID where b.ClientID='+@id+@condition)
else
begin
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
set @PageLowerBound=@PageLowerBound+1
exec('declare @indextable table(id int identity(1,1),nid int)
insert into @indextable(nid) select a.ClientID from web_Clients a inner join web_Clients_Partners b on a.ClientID=PartnerID where b.ClientID='+@id+@condition+' order by b.AddDate desc
select a.ClientID as userid,ClientName as username,ClientAliasName,contactMan,phone,mobiphone,
(select distinct DName from Common_DCode c where c.dCode=a.AreaCode) ProvName,
b.StatusCode,RelationCode,b.AddDate,CleintTypesCode
from web_Clients a inner join web_Clients_Partners b on a.ClientID=PartnerID , @indextable t WHERE b.ClientID='+@id+@condition+' and a.ClientID=t.nid
and t.id between '+@PageLowerBound+' and '+@PageUpperBound+'
order by t.id ')
end
set nocount off
GO