CREATE PROCEDURE BCP_transaction_BillList
(
@Page_No nvarchar(10) = '1',
@Page_Size nvarchar(10) = '10',
@Record_Count bigint = 0 output,
@TID nvarchar(60) = '',
@NO nvarchar(30) = '',
@CPID nvarchar(30) = '',
@CPName nvarchar(50) = '',
@TransType nvarchar(10) = '',
@SItemPrice int ,
@EItemPrice int ,
@SDate nvarchar(8) = '',
@EDate nvarchar(8) = '',
@OrderID nvarchar(80) = '',
@UserID nvarchar(50) = '',
@totalAmt money = 0 output
)
AS
BEGIN
set nocount on
declare @sSQL nvarchar(4000), @sSQLList nvarchar(4000), @sSQLCount nvarchar(4000), @sSQLAmt nvarchar(4000), @sSub nvarchar(4000)
IF @SItemPrice IS NULL or @SItemPrice = ''
SET @SItemPrice = 0
IF @EItemPrice IS NULL or @SItemPrice = ''
SET @EItemPrice = 9999 declare @subPage_Size nvarchar(10)
set @subPage_Size = Convert(nvarchar(10),((Convert(int,@Page_No) - 1) * Convert(int,@Page_Size)))
set @sSQLList = '
select top ' + @Page_Size + ' tid, T_ORDER.no, name, provider_id, TransType, bank_transAmt, bank_sysDate, bank_sysSeqid, user_id '
set @sSQLCount = '
select count(*) '
set @sSQLAmt = '
select sum(bank_transAmt) '
set @sSQL = '
from T_ORDER , T_PROVIDER
where T_ORDER.provider_id = T_PROVIDER.no and bank_transAmt between ' + convert(nvarchar(10), @SItemPrice) + ' and ' + convert(nvarchar(10), @EItemPrice)
IF @SDate <> '' AND @EDate <> ''
SET @sSQL = @sSQL + '
and bank_sysDate between ''' + @SDate +''' and ''' + @EDate +''''
if isnull(@CPID, '') <> ''
set @sSQL = @sSQL + '
and cprovider_id = ''' + @CPID + ''''
if isnull(@CPName, '') <> ''
set @sSQL = @sSQL + '
and name like ''%' + @CPName + '%'''
if isnull(@TID, '') <> ''
set @sSQL = @sSQL + '
and tid = ''' + @TID + ''''
if isnull(@NO, '') <> ''
set @sSQL = @sSQL + '
and no = ''' + @NO + '''' if isnull(@OrderID, '') <> ''
set @sSQL = @sSQL + '
and bank_sysSeqid like ''%' + @OrderID + '%'' '
IF isnull(@UserID, '') <> ''
SET @sSQL = @sSQL + '
and User_ID like ''%' + @UserID + '%''' set @sSub = N' and TID not in (select top ' + @subPage_Size + ' TID'
set @sSub = @sSub + @sSQL + ' order by TID desc)'
set @sSQLCount = @sSQLCount + @sSQL
print @sSQLCount
EXEC (@sSQLCount)
set @sSQLAmt = @sSQLAmt + @sSQL
print @sSQLAmt
EXEC (@sSQLAmt)
set @sSQLList = @sSQLList + @sSQL + @sSub + ' order by tid desc'
print @sSQLList
EXEC (@sSQLList) set nocount off
END
GO
(
@Page_No nvarchar(10) = '1',
@Page_Size nvarchar(10) = '10',
@Record_Count bigint = 0 output,
@TID nvarchar(60) = '',
@NO nvarchar(30) = '',
@CPID nvarchar(30) = '',
@CPName nvarchar(50) = '',
@TransType nvarchar(10) = '',
@SItemPrice int ,
@EItemPrice int ,
@SDate nvarchar(8) = '',
@EDate nvarchar(8) = '',
@OrderID nvarchar(80) = '',
@UserID nvarchar(50) = '',
@totalAmt money = 0 output
)
AS
BEGIN
set nocount on
declare @sSQL nvarchar(4000), @sSQLList nvarchar(4000), @sSQLCount nvarchar(4000), @sSQLAmt nvarchar(4000), @sSub nvarchar(4000)
IF @SItemPrice IS NULL or @SItemPrice = ''
SET @SItemPrice = 0
IF @EItemPrice IS NULL or @SItemPrice = ''
SET @EItemPrice = 9999 declare @subPage_Size nvarchar(10)
set @subPage_Size = Convert(nvarchar(10),((Convert(int,@Page_No) - 1) * Convert(int,@Page_Size)))
set @sSQLList = '
select top ' + @Page_Size + ' tid, T_ORDER.no, name, provider_id, TransType, bank_transAmt, bank_sysDate, bank_sysSeqid, user_id '
set @sSQLCount = '
select count(*) '
set @sSQLAmt = '
select sum(bank_transAmt) '
set @sSQL = '
from T_ORDER , T_PROVIDER
where T_ORDER.provider_id = T_PROVIDER.no and bank_transAmt between ' + convert(nvarchar(10), @SItemPrice) + ' and ' + convert(nvarchar(10), @EItemPrice)
IF @SDate <> '' AND @EDate <> ''
SET @sSQL = @sSQL + '
and bank_sysDate between ''' + @SDate +''' and ''' + @EDate +''''
if isnull(@CPID, '') <> ''
set @sSQL = @sSQL + '
and cprovider_id = ''' + @CPID + ''''
if isnull(@CPName, '') <> ''
set @sSQL = @sSQL + '
and name like ''%' + @CPName + '%'''
if isnull(@TID, '') <> ''
set @sSQL = @sSQL + '
and tid = ''' + @TID + ''''
if isnull(@NO, '') <> ''
set @sSQL = @sSQL + '
and no = ''' + @NO + '''' if isnull(@OrderID, '') <> ''
set @sSQL = @sSQL + '
and bank_sysSeqid like ''%' + @OrderID + '%'' '
IF isnull(@UserID, '') <> ''
SET @sSQL = @sSQL + '
and User_ID like ''%' + @UserID + '%''' set @sSub = N' and TID not in (select top ' + @subPage_Size + ' TID'
set @sSub = @sSub + @sSQL + ' order by TID desc)'
set @sSQLCount = @sSQLCount + @sSQL
print @sSQLCount
EXEC (@sSQLCount)
set @sSQLAmt = @sSQLAmt + @sSQL
print @sSQLAmt
EXEC (@sSQLAmt)
set @sSQLList = @sSQLList + @sSQL + @sSub + ' order by tid desc'
print @sSQLList
EXEC (@sSQLList) set nocount off
END
GO
(
Page_Non in varchar2,
Page_Sizen in varchar2,
Record_Count out int,
TIDn in varchar2,
NOn in varchar2,
CPID in varchar2,
CPNamen in varchar2,
TransTypen in varchar2,
SItemPrice in int,
EItemPrice in int,
SDaten in varchar2,
EDaten in varchar2,
OrderIDn in varchar2,
UserIDn in varchar2,
totalAmt out number
)
is
sSQL varchar2(4000);
sSQLList varchar2(4000);
sSQLCount varchar2(4000);
sSQLAmt varchar2(4000);
sSub varchar2(4000);
subPage_Size varchar2(10);
BEGINIF SItemPrice IS NULL or SItemPrice = '' then
SItemPrice:= 0;
end if;IF EItemPrice IS NULL or SItemPrice = '' then
EItemPrice:= 9999
end if;......end;