ALTER PROCEDURE sp_BankKPI_BuildOperationFlowByManager
@dtStartTime DateTime ,
@dtEndTime DateTime,
@strClientMgr nvarchar(256),
@strClientName nvarchar(256),
@iResult int = 0 OUTPUT
AS
begin declare @strSQL nvarchar(4000);
declare @strFields nvarchar(4000);
declare @strValues nvarchar(4000);
set @strFields='';
set @strValues='';
--中间业务
declare @ProduceKind nvarchar(256);
declare @Mid_I money;
declare @Mid_EA money;
declare @Mid_DY money;
declare @Mid_Income money;
declare @Mid_EAIncome money;
declare @Mid_DYIncome money;
declare @Mid_Rate float;
declare sp_BankKPI_MidOperation_Cursor cursor for SELECT DISTINCT ProduceKind FROM tblBankAssess_MidOperation t left join tblBankAssess_OperationDetail a
on t.contentid=a.operationid and a.ClientMgr=@strClientMgr and ImprotDate between @dtStartTime and @dtEndTime
open sp_BankKPI_MidOperation_Cursor
Fetch next from sp_BankKPI_MidOperation_Cursor into @ProduceKind
while @@Fetch_Status =0
Begin
set @Mid_I=0;
set @Mid_EA=0;
set @Mid_DY=0;
declare sp_BankKPI_MidOperationProduce_Cursor cursor for select Income,EAIncome,DYIncome,Rate from tblBankAssess_MidOperation as t
left join tblBankAssess_OperationDetail a on t.contentid=a.operationid where a.ClientMgr=@strClientMgr and
a.ClientName=@strClientName and ImprotDate between @dtStartTime and @dtEndTime and ProduceKind=@ProduceKind
open sp_BankKPI_MidOperationProduce_Cursor
Fetch next from sp_BankKPI_MidOperationProduce_Cursor into @Mid_Income,@Mid_EAIncome,@Mid_DYIncome,@Mid_Rate
while @@Fetch_Status =0
Begin
set @Mid_I=@Mid_I+@Mid_Income*@Mid_Rate/100;
set @Mid_EA=@Mid_EA+@Mid_EAIncome*@Mid_Rate/100;
set @Mid_DY=@Mid_DY+@Mid_DYIncome*@Mid_Rate/100;
Fetch next from sp_BankKPI_MidOperationProduce_Cursor into @Mid_Income,@Mid_EAIncome,@Mid_DYIncome,@Mid_Rate
End
close sp_BankKPI_MidOperationProduce_Cursor
DEALLOCATE sp_BankKPI_MidOperationProduce_Cursor
set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'金额');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_Income);
set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'入账收入');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_EA);
set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'递延收入');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_DY);
Fetch next from sp_BankKPI_MidOperation_Cursor into @ProduceKind
End
close sp_BankKPI_MidOperation_Cursor
DEALLOCATE sp_BankKPI_MidOperation_Cursor
--执行插入
set @strSQL='insert tblBankAssess_OperationFlow (ClientMgr,ClientName,Begindate,Enddate'+@strFields
+') values('''+@strClientMgr+''','''+@strClientName+''','''+ CONVERT(varchar(256),@dtStartTime)+''','''+ CONVERT(varchar(256),@dtEndTime)+''''+@strValues+')';
exec sp_executesql @strSQL;
return 1;
end
游标有错吗??
@dtStartTime DateTime ,
@dtEndTime DateTime,
@strClientMgr nvarchar(256),
@strClientName nvarchar(256),
@iResult int = 0 OUTPUT
AS
begin declare @strSQL nvarchar(4000);
declare @strFields nvarchar(4000);
declare @strValues nvarchar(4000);
set @strFields='';
set @strValues='';
--中间业务
declare @ProduceKind nvarchar(256);
declare @Mid_I money;
declare @Mid_EA money;
declare @Mid_DY money;
declare @Mid_Income money;
declare @Mid_EAIncome money;
declare @Mid_DYIncome money;
declare @Mid_Rate float;
declare sp_BankKPI_MidOperation_Cursor cursor for SELECT DISTINCT ProduceKind FROM tblBankAssess_MidOperation t left join tblBankAssess_OperationDetail a
on t.contentid=a.operationid and a.ClientMgr=@strClientMgr and ImprotDate between @dtStartTime and @dtEndTime
open sp_BankKPI_MidOperation_Cursor
Fetch next from sp_BankKPI_MidOperation_Cursor into @ProduceKind
while @@Fetch_Status =0
Begin
set @Mid_I=0;
set @Mid_EA=0;
set @Mid_DY=0;
declare sp_BankKPI_MidOperationProduce_Cursor cursor for select Income,EAIncome,DYIncome,Rate from tblBankAssess_MidOperation as t
left join tblBankAssess_OperationDetail a on t.contentid=a.operationid where a.ClientMgr=@strClientMgr and
a.ClientName=@strClientName and ImprotDate between @dtStartTime and @dtEndTime and ProduceKind=@ProduceKind
open sp_BankKPI_MidOperationProduce_Cursor
Fetch next from sp_BankKPI_MidOperationProduce_Cursor into @Mid_Income,@Mid_EAIncome,@Mid_DYIncome,@Mid_Rate
while @@Fetch_Status =0
Begin
set @Mid_I=@Mid_I+@Mid_Income*@Mid_Rate/100;
set @Mid_EA=@Mid_EA+@Mid_EAIncome*@Mid_Rate/100;
set @Mid_DY=@Mid_DY+@Mid_DYIncome*@Mid_Rate/100;
Fetch next from sp_BankKPI_MidOperationProduce_Cursor into @Mid_Income,@Mid_EAIncome,@Mid_DYIncome,@Mid_Rate
End
close sp_BankKPI_MidOperationProduce_Cursor
DEALLOCATE sp_BankKPI_MidOperationProduce_Cursor
set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'金额');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_Income);
set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'入账收入');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_EA);
set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'递延收入');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_DY);
Fetch next from sp_BankKPI_MidOperation_Cursor into @ProduceKind
End
close sp_BankKPI_MidOperation_Cursor
DEALLOCATE sp_BankKPI_MidOperation_Cursor
--执行插入
set @strSQL='insert tblBankAssess_OperationFlow (ClientMgr,ClientName,Begindate,Enddate'+@strFields
+') values('''+@strClientMgr+''','''+@strClientName+''','''+ CONVERT(varchar(256),@dtStartTime)+''','''+ CONVERT(varchar(256),@dtEndTime)+''''+@strValues+')';
exec sp_executesql @strSQL;
return 1;
end
游标有错吗??
@dtStartTime DateTime ,
@dtEndTime DateTime,
@strClientMgr nvarchar(256),
@strClientName nvarchar(256),
@iResult int = 0 OUTPUT
AS
begin declare @strSQL nvarchar(4000);
declare @strFields nvarchar(4000);
declare @strValues nvarchar(4000); set @strFields='';
set @strValues='';
--中间业务
declare @ProduceKind nvarchar(256);
declare @Mid_I money;
declare @Mid_EA money;
declare @Mid_DY money;
declare @Mid_Income money;
declare @Mid_EAIncome money;
declare @Mid_DYIncome money;
declare @Mid_Rate float; declare sp_BankKPI_MidOperation_Cursor cursor for SELECT DISTINCT ProduceKind FROM tblBankAssess_MidOperation t left join tblBankAssess_OperationDetail a
on t.contentid=a.operationid and a.ClientMgr=@strClientMgr and ImprotDate between @dtStartTime and @dtEndTime
open sp_BankKPI_MidOperation_Cursor
Fetch next from sp_BankKPI_MidOperation_Cursor into @ProduceKind
while @@Fetch_Status =0
Begin
set @Mid_I=0;
set @Mid_EA=0;
set @Mid_DY=0; declare sp_BankKPI_MidOperationProduce_Cursor cursor for select Income,EAIncome,DYIncome,Rate from tblBankAssess_MidOperation as t
left join tblBankAssess_OperationDetail a on t.contentid=a.operationid where a.ClientMgr=@strClientMgr and
a.ClientName=@strClientName and ImprotDate between @dtStartTime and @dtEndTime and ProduceKind=@ProduceKind
open sp_BankKPI_MidOperationProduce_Cursor
Fetch next from sp_BankKPI_MidOperationProduce_Cursor into @Mid_Income,@Mid_EAIncome,@Mid_DYIncome,@Mid_Rate
while @@Fetch_Status =0
Begin
set @Mid_I=@Mid_I+@Mid_Income*@Mid_Rate/100;
set @Mid_EA=@Mid_EA+@Mid_EAIncome*@Mid_Rate/100;
set @Mid_DY=@Mid_DY+@Mid_DYIncome*@Mid_Rate/100; Fetch next from sp_BankKPI_MidOperationProduce_Cursor into @Mid_Income,@Mid_EAIncome,@Mid_DYIncome,@Mid_Rate
End
close sp_BankKPI_MidOperationProduce_Cursor
DEALLOCATE sp_BankKPI_MidOperationProduce_Cursor set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'金额');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_Income); set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'入账收入');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_EA); set @strFields=@strFields+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'递延收入');
set @strValues=@strValues+','+CONVERT(varchar(256),@Mid_DY);
Fetch next from sp_BankKPI_MidOperation_Cursor into @ProduceKind
End
close sp_BankKPI_MidOperation_Cursor
DEALLOCATE sp_BankKPI_MidOperation_Cursor --执行插入
set @strSQL='insert tblBankAssess_OperationFlow (ClientMgr,ClientName,Begindate,Enddate'+@strFields
+') values('''+@strClientMgr+''','''+@strClientName+''','''+ CONVERT(varchar(256),@dtStartTime)+''','''+ CONVERT(varchar(256),@dtEndTime)+''''+@strValues+')';
exec sp_executesql @strSQL;
return 1;
end
set @strFields=isnull(@strFields,'')+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'金额');
set @strValues=isnull(@strValues,'')+','+CONVERT(varchar(256),@Mid_Income);set @strFields=isnull(@strFields,'')+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'入账收入');
set @strValues=isnull(@strValues,'')+','+CONVERT(varchar(256),@Mid_EA);set @strFields=isnull(@strFields,'')+','+dbo.fn_BankKPI_GetOperationFlowFieldID(@ProduceKind,'递延收入');
set @strValues=isnull(@strValues,'')+','+CONVERT(varchar(256),@Mid_DY);
declare @s varchar(50)
select @s --这是nuluselect @s+'abc' --这里还是nullnull+字符=null
set @strFields='';
set @strValues='';
--这看看连的字符都是什么
select @strClientMgr--比如第一个
--执行插入
set @strSQL='insert tblBankAssess_OperationFlow (ClientMgr,ClientName,Begindate,Enddate'+@strFields
+') values('''+@strClientMgr+''','''+@strClientName+''','''+ CONVERT(varchar(256),@dtStartTime)+''','''+ CONVERT(varchar(256),@dtEndTime)+''''+@strValues+')';--这看看@strSQL的值是什么
select strSQL
exec sp_executesql @strSQL;太长了,没看好,按这个再检查一下
这个放哪??
----------楼上
select @strClientMgr--比如第一个--执行插入
set @strSQL='insert tblBankAssess_OperationFlow (ClientMgr,ClientName,Begindate,Enddate'+@strFields
+') values('''+@strClientMgr+''','''+@strClientName+''','''+ CONVERT(varchar(256),@dtStartTime)+''','''+ CONVERT(varchar(256),@dtEndTime)+''''+@strValues+')';--这看看@strSQL的值是什么
select strSQL
--exec sp_executesql @strSQL; --这个先注了,看看语句对不对
----
出错,列名 'strSQL' 无效。我对储存过程不太懂,麻烦说的详细点