存储过程分页,以及动态sql(Sql server)
/*
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