set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[selectP]
@typeid varchar(256), -- 表名
@flag nvarchar(20)
as
declare
@Parameter nvarchar(20),
@options nvarchar(20),
@times int, --条件
@Days int,
@Summoney money,
@Nowmoney int,
@Maxmoney int,
@Minmoney int,
@type nvarchar(2),
@strSQL varchar(5000), -- 主语句
@tmpSQL varchar(5000),
@DaysSQL varchar(5000), --条件SQL语句
@timesSQL varchar(5000),
@nowmoneySQL varchar(5000),
@MaxmoneySQL varchar(5000),
@MinmoneySQL varchar(5000)
begin
select @times=0,@Days=0,@Summoney=0,@nowmoney=0
declare mycursor cursor for
select Parameter,options from AML_ConditionOptions where typeid=@typeid
open mycursor
fetch next from mycursor into @Parameter,@options
while(@@fetch_status=0)
begin
if @options = 'Times'
begin
set @times=@parameter
end
if @options = 'Days'
begin
set @Days=@parameter
end
if @options = 'Summoney'
begin
set @Summoney=@parameter
end
if @options = 'nowmoney'
begin
set @nowmoney=@parameter
end
fetch next from mycursor into @Parameter,@options
end
close mycursor
deallocate mycursor
if @flag = '1'
begin
select * into #AML_TempDATA from AML_DATA where CSNM in ( select account from AML_BlackList)
set @tmpSQL ='#AML_TempDATA'
end
else
begin
set @tmpSQL ='AML_DATA'
end
create table #CSNMtable(
CSNM nvarchar(50)
)
if @Days = '0'
begin
set @DaysSQL = ''
end
else
begin
set @DaysSQL = '
union select CSNM from '+ @tmpSQL + '
group by CSNM,TSTM
having sum(convert(int,CRAT)) > '+str(@Summoney)+'
and count(Convert(char,TSTM)+CSNM) > '+str(@Days)
end
if @times = '0'
begin
set @timesSQL = ''
end
else
begin
set @timesSQL =
' union select CSNM from '+ @tmpSQL + '
group by CSNM
having count(distinct Convert(char,TSTM)+CSNM)> '+str(@times)+'-1 and sum(Convert(int,CRAT))> '+str(@Summoney)
end
if @Nowmoney = '0'
begin
set @NowmoneySQL = ''
end
else
begin
set @NowmoneySQL = '
union select CSNM from '+ @tmpSQL + '
group by CSNM,TSTM
having sum(convert(int,CRAT)) > '+str(@Nowmoney)
end
set @strSQL = '
select CSNM,CTNM into #table from AML_DATA where CSNM in ( select CSNM from #CSNMtable '+ @DaysSQL + @timesSQL +')
select CSNM,CTNM from #table '
--print @strSQL
create table #table(
--ID int,
--CTAC nvarchar(50),--账号
CSNM nvarchar(50),--客户号
CTNM nvarchar(50)--客户名称
--TSTM nvarchar(50),--交易时间
--TSTP nvarchar(50),--交易方式
--TDRC nvarchar(50),--交易去向
--TRCD nvarchar(50),--交易发生地
--CRTP nvarchar(50),--币种
--CRAT nvarchar(50)--交易金额
)--声明一个临时表
insert into #table exec (@strSQL)
--select * from #table --使用你的临时表
--set @tblName = '#table'
select CSNM,CTNM from #table group by CSNM,CTNM
end
--exec selectP '0001','0'