存储过程

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'