推荐订阅源

/*
CopyRight  2005 www.zte.com.cn  All rights reserved.
系统名称  ：互联星空
子系统名称：资源管理子系统
描述      ：检索SP订购关系列表
作者      ：吴岸城
创建日期  ：2005-12-13
*/

alter procedure dbo.Up_Res_SearchSPOrderInfo
(
 @SPID varchar(60),
 @ServiceID varchar(60),
 @UserID varchar(60), 
 @SubscriptionDate varchar(60),
 @SubscriptionDateEnd varchar(60),
 @Status varchar(60) ,
 @CountOnePage int,
 @Pages int
)
as
declare @SqlString nvarchar(1000)

set  @SqlString =' SELECT TOP '+@CountOnePage+' a.TxID, a.UserID, a.ChargeID, a.SPID, a.ServiceID, a.SubscriptionDate,
 a.BeginDate, a.EndDate, a.Status, b.SPName, b.ServiceName
 FROM   spms_ServiceSubscription a LEFT OUTER JOIN
   vwResourceSPService b ON a.ServiceID = b.ServiceID
 
 WHERE   (b.ServiceID>(SELECT MAX(b.ServiceID)FROM
   (SELECT TOP '+@Pages+' b.ServiceID FROM spms_ServiceSubscription a LEFT OUTER JOIN
     vwResourceSPService b ON a.ServiceID = b.ServiceID ORDER BY b.ServiceID)
  AST)) '

if (@SPID is not null and @SPID<>'')
set @SqlString=@SqlString+' and a.SPID = @SPID '

if (@ServiceID is not null and @ServiceID<>'')
set @SqlString=@SqlString +' and a.ServiceID = @ServiceID '

if (@UserID is not null and @UserID<>'')
set @SqlString=@SqlString +' and a.UserID = @UserID '

if (@Status is not null and @Status<>-1)
set @SqlString=@SqlString +' and a.Status = @Status '

if (@SubscriptionDate is not null) and (@SubscriptionDate <> '')
 set @SqlString = @SqlString + ' and a.SubscriptionDate >= ''' + @SubscriptionDate + ' 00:00:00'''
  
if (@SubscriptionDateEnd is not null) and (@SubscriptionDateEnd <> '')
 set @SqlString = @SqlString + ' and a.SubscriptionDate <= ''' + @SubscriptionDateEnd + ' 23:59:59'''

EXEC sp_executesql @SqlString
RETURN