问题遇到的现象和发生背景
数据推送多行至其他接口
问题相关代码,请勿粘贴截图
SQL:
--新增的已办;上次有本次没有,判断为未发送的已办
DECLARE T_ISSUE_INFOS_HIS CURSOR FOR
select ISSUENUMBER,NODE from [dbo].[T_IR_ToDo_M]
Except
select ISSUENUMBER,NODE from [dbo].[T_IR_ToDo_Tmp]
--打开游标 wblrh 2022-5-19
OPEN T_ISSUE_INFOS_HIS
SET @STEP='7.BEGIN FETCH.'
--从游标里取出数据赋值到变量中
FETCH NEXT FROM T_ISSUE_INFOS_HIS INTO @ISSUENUMBER, @NODE
WHILE ( @@FETCH_STATUS = 0 ) --如果上一次操作成功则继续循环
BEGIN
--流程ID
SET @APPROVEID = @ISSUENUMBER + @NODE;
SELECT @APPROVEID=DBO.F_GetPendingID(@APPROVEID,1);
SELECT @DOCOUNT = COUNT(*) FROM [dbo].[T_IR_Done] WHERE PROCESS_ID = @APPROVEID
IF @DOCOUNT = 0
BEGIN
SET @STEP='8.INSERT T_IR_Done.'
INSERT INTO [T_IR_Done]
(PROCESS_ID,
LAST_UPDATED_BY,
LAST_UPDATED_TIME,
HANDLE_TEXT,
PROCESS_STATUS,
PROCESS_RUNTIME_ID,
system_id,
process_pending_id,
created_by,
pend_autor,
[topic],
created_time,
process_module_id,
process_kind_guid,
process_kind_name,
owner,
owner_name,
last_autor,
[url],
main_ver,
sub_ver,
active_date,
maxapprovalhours,
maxapprovaldate,
pending_type,
initiate_datetime,
process_name,
active_month,
current_step_id,
current_step_name
)
SELECT DBO.F_GetPendingID( @ISSUENUMBER + @NODE ,2) PROCESS_ID,
isnull(I.[MODIFIER],'1') LAST_UPDATED_BY,
isnull(I.[LAST_UPDATED_DATE],GETDATE()) LAST_UPDATED_TIME,
' ' HANDLE_TEXT,
case when i.status in ('结案','已结案') then '4'
when i.status='无效' then '3'
when i.status is null then '2'
else '1' end PROCESS_STATUS,
A.ISSUENUMBER,
'DQbase',
i.ISSUE_INFO_ID,
isnull(ruc.user_id,'1'),
isnull(au.user_id,'1') ,
i.TITLE,
i.create_time,
'DQBASE_FW',
'DQBASE_FW',
'新车试作案件管理',
isnull(u.user_id,'1'),
isnull(e.emp_name,'系统管理员') ,
isnull(u.user_id,'1'),
v.[VALUEDES] as [url],
0,
0,
a.LAST_UPDATED_DATE,
10080,
a.LAST_UPDATED_DATE,
'2',
a.LAST_UPDATED_DATE,
'新车试作案件管理',
CONVERT(varchar(6),a.LAST_UPDATED_DATE,112),
@NODE,
@NODE
from [dbo].[T_ISSUE_INFO] i
left join (SELECT TOP 1 APPROVE_ID,ISSUENUMBER,APPROVESTATE,CREATOR,CREATE_TIME,MODIFIER,LAST_UPDATED_DATE
FROM [dbo].[T_APPROVAL_RESULT]
WHERE ISSUENUMBER=@ISSUENUMBER
ORDER BY LAST_UPDATED_DATE DESC) r on i.ISSUENUMBER=r.ISSUENUMBER
left join [dbo].[T_DUTY_ASSGIN] a on i.ISSUENUMBER=a.ISSUENUMBER
left join [dbo].[t_eap_sys_users] u on i.CREATOR=u.user_name
left join [dbo].[t_eap_sys_employees] e on u.emp_id=e.emp_id
left join [dbo].[t_eap_sys_employees] ae on a.CURRENTPERSON=ae.emp_id
left join [dbo].[t_eap_sys_users] au on ae.emp_id=au.emp_id
left join [dbo].[t_eap_sys_users] ruc on r.CREATOR=ruc.user_name
left join [dbo].[t_eap_sys_users] rum on r.MODIFIER=rum.user_name
left join [dbo].[TB_LOOK_VALUE] v on v.[TYPE]='DQBaseWebUrl' and v.[TYPENAME]=@NODE
WHERE @ISSUENUMBER+@NODE NOT IN
(SELECT PROCESS_ID FROM [T_IR_Done])
END ELSE
BEGIN
SET @STEP='9.UPDATE T_IR_Done.'
UPDATE T_IR_Done
SET [process_module_id]=TB.[process_module_id],
[process_runtime_id]=TB.[process_runtime_id],
[process_kind_guid]=TB.[process_kind_guid],
[process_kind_name]=TB.[process_kind_name],
[process_pending_id]=TB.[process_pending_id],
[topic]=TB.[topic],
[owner]=TB.[owner],
[owner_name]=TB.[owner_name],
[last_autor]=TB.[last_autor],
[pend_autor]=TB.[pend_autor],
[initiate_datetime]=TB.[initiate_datetime],
[process_name]=TB.[process_name],
[current_step_id]=TB.[current_step_id],
[current_step_name]=TB.[current_step_name],
[system_id]=TB.[system_id],
[url]=TB.[url],
[active_date]=TB.[active_date],
[maxapprovalhours]=TB.[maxapprovalhours],
[active_month]=TB.[active_month],
[created_by]=TB.[created_by],
[created_time]=TB.[created_time],
[last_updated_by]=TB.[last_updated_by],
[last_updated_time]=TB.[last_updated_time],
[pending_type]=TB.[pending_type]
FROM (SELECT @APPROVEID as [process_id],
'DQBASE_FW' as [process_module_id],
SUBSTRING(i.ISSUENUMBER, 1, CHARINDEX('-', i.ISSUENUMBER)) + SUBSTRING(i.ISSUENUMBER, CHARINDEX('-', i.ISSUENUMBER) + 1, 5) as [process_runtime_id],
'DQBASE_FW' as [process_kind_guid],
'新车试作案件管理' as [process_kind_name],
i.ISSUE_INFO_ID as [process_pending_id],
i.TITLE as [topic],
isnull(u.user_id,'1') as [owner],
isnull(e.emp_name,'系统管理员') as [owner_name],
isnull(u.user_id,'1') as [last_autor],
isnull(au.user_id,'1') as [pend_autor],
a.LAST_UPDATED_DATE as [initiate_datetime],
'新车试作案件管理' as [process_name],
@NODE as [current_step_id],
@NODE as [current_step_name],
'DQbase' as [system_id],
v.[VALUEDES] as [url],
a.LAST_UPDATED_DATE as [active_date],
10080 as [maxapprovalhours],
CONVERT(varchar(6),a.LAST_UPDATED_DATE,112) as [active_month],
isnull(ruc.user_id,'1') as [created_by],
isnull(r.CREATE_TIME,getdate()) as [created_time],
isnull(rum.user_id,'1') as [last_updated_by],
isnull(r.LAST_UPDATED_DATE,getdate()) as [last_updated_time],
case when r.[APPROVESTATE]='1' then '4' when r.[APPROVESTATE]='0' then '6' else '1' end [pending_type]
from [dbo].[T_ISSUE_INFO] i
left join (SELECT TOP 1 APPROVE_ID,ISSUENUMBER,APPROVESTATE,CREATOR,CREATE_TIME,MODIFIER,LAST_UPDATED_DATE
FROM [dbo].[T_APPROVAL_RESULT]
WHERE ISSUENUMBER=@ISSUENUMBER
ORDER BY LAST_UPDATED_DATE DESC) r on i.ISSUENUMBER=r.ISSUENUMBER
left join [dbo].[T_DUTY_ASSGIN] a on i.ISSUENUMBER=a.ISSUENUMBER
left join [dbo].[t_eap_sys_users] u on i.CREATOR=u.user_name
left join [dbo].[t_eap_sys_employees] e on u.emp_id=e.emp_id
left join [dbo].[t_eap_sys_employees] ae on a.CURRENTPERSON=ae.emp_id
left join [dbo].[t_eap_sys_users] au on ae.emp_id=au.emp_id
left join [dbo].[t_eap_sys_users] ruc on r.CREATOR=ruc.user_name
left join [dbo].[t_eap_sys_users] rum on r.MODIFIER=rum.user_name
left join [dbo].[TB_LOOK_VALUE] v on v.[TYPE]='DQBaseWebUrl' and v.[TYPENAME]=@NODE
where i.ISSUENUMBER=@ISSUENUMBER
) TB
WHERE T_IR_Done.PROCESS_ID=TB.PROCESS_ID;
END
FETCH NEXT FROM T_ISSUE_INFOS_HIS INTO @ISSUENUMBER, @NODE
END
SET @STEP='10.CLOSE T_ISSUE_INFOS_HIS.'
--关闭游标
CLOSE T_ISSUE_INFOS_HIS ;
--撤销游标(释放资源)
DEALLOCATE T_ISSUE_INFOS_HIS ;
运行结果及报错内容
数据推送异常
我的解答思路和尝试过的方法
调试存储过程游标
我想要达到的结果
数据推送为一行数据