lyfz_repos/铂金版源码/DBFile/SQL/获取客户记录集.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_GetCustomerRecord]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_GetCustomerRecord]
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		刘超
-- Create date: 2015-07-16
-- Description:	获取客户记录集
-- =============================================
CREATE  PROCEDURE [dbo].[PROCE_GetCustomerRecord]
(
 @AggregationCustomerFieldsStr varchar(4000) = '*',   --聚合客户表字段名(全部字段为*)
 @GroupMembersAndCustomerFieldsStr varchar(4000) = '*',   --组成员视图字段名(全部字段为*)
 @OrderString varchar(2000),         --排序字段(必须!支持多字段不用加order by)
 @WhereString varchar(2000) =N'',  --条件语句(不用加where)
 @PageSize int,                     --每页多少条记录
 @PageIndex int = 1 ,               --指定当前为第几页
 @TotalRecord int output            --返回总记录数       --返回更新记录数
)
AS
BEGIN    

 --处理开始点和结束点
    Declare @AG_TableName nvarchar(2000); 
    Declare @GM_TableName nvarchar(2000);   
    set @AG_TableName='[tempTB_GroupByAggregationCustomer]'--'[tempTB_AggregationCustomer]'
   -- set @GM_TableName='dbo.View_CustomerGroupMembersAndErpCustomer'
    Declare @GM_SqlString nvarchar(4000); 
    set @GM_SqlString='
DECLARE	@TotalCount int
DECLARE	@tabRecord TABLE 
(
  GP_CustomerGroupID NVARCHAR(50) NOT NULL
)
 insert into @tabRecord exec [dbo].[PROCE_SQL2005PAGECHANGE]
		@TableName = '''+@AG_TableName+''',
		@ReFieldsStr = ''GP_CustomerGroupID'',
		@OrderString = '''+@OrderString+''',
		@WhereString = '''+Replace(@WhereString,'''','''''')+''',
		@PageSize = '+CONVERT(varchar(200), @PageSize)+',
		@PageIndex = '+CONVERT(varchar(200), @PageIndex)+',
		@TotalRecord = @TotalCount OUTPUT
select '+@GroupMembersAndCustomerFieldsStr+' 
 FROM [dbo].[View_CustomerGroupMembersAndErpCustomer]
 where GM_CustomerGroupID in(select [GP_CustomerGroupID] from @tabRecord)
 order by GM_CustomerGroupID asc, GM_ProtagonistCustomer desc, GM_Master desc'
--select @GM_SqlString
exec(@GM_SqlString)

exec [dbo].[PROCE_SQL2005PAGECHANGE]
		@TableName = @AG_TableName,
		@ReFieldsStr = @AggregationCustomerFieldsStr,
		@OrderString = @OrderString,
		@WhereString = @WhereString,
		@PageSize = @PageSize,
		@PageIndex = @PageIndex,
		@TotalRecord = @TotalRecord OUTPUT

END

GO