set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO
ALTER PROCEDURE [dbo].[selectP]
@typeid varchar(256), -- 表名 @flag nvarchar(20)asdeclare @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)beginselect @times=0,@Days=0,@Summoney=0,@nowmoney=0declare mycursor cursor forselect Parameter,options from AML_ConditionOptions where typeid=@typeidopen mycursorfetch next from mycursor into @Parameter,@optionswhile(@@fetch_status=0)beginif @options = 'Times'begin set @times=@parameterendif @options = 'Days'begin set @Days=@parameterendif @options = 'Summoney'begin set @Summoney=@parameterendif @options = 'nowmoney'begin set @nowmoney=@parameterend
fetch next from mycursor into @Parameter,@optionsend close mycursordeallocate mycursor
if @flag = '1'beginselect * into #AML_TempDATA from AML_DATA where CSNM in ( select account from AML_BlackList)set @tmpSQL ='#AML_TempDATA'endelsebeginset @tmpSQL ='AML_DATA'end
create table #CSNMtable(CSNM nvarchar(50))
if @Days = '0'beginset @DaysSQL = ''endelsebeginset @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'beginset @timesSQL = ''endelsebeginset @timesSQL = ' union select CSNM from '+ @tmpSQL + 'group by CSNMhaving count(distinct Convert(char,TSTM)+CSNM)> '+str(@times)+'-1 and sum(Convert(int,CRAT))> '+str(@Summoney)end
if @Nowmoney = '0'beginset @NowmoneySQL = ''endelsebeginset @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 @strSQLcreate 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'
转载于:https://www.cnblogs.com/hengbo/archive/2009/03/24/2232504.html
