单表的自修改与自插入数据（重要）-CSDN博客

USE [ChiefMes]
GO

/****** Object: StoredProcedure [dbo].[Kenta_UpandAddEmpOEEConfig] Script Date: 05/12/2015 08:34:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Kenta_UpandAddEmpOEEConfig]
(
@IDStr nvarchar(100)=''
)
AS
begin

if(@IDStr<>'')
begin
--declare @splitTable Table (
--EmpID nvarchar(20) not null
--)
--insert into @splitTable
--select Col from dbo.FN_SplitSTR(@EmpIDStr,',') fstt

update A set A.EndDate=DATEADD(dd,-1,GETDATE())
from Kenta_EmpOEEConfig A
join
(select * from Kenta_EmpOEEConfig p
where p.id=(select max(id) from Kenta_EmpOEEConfig a where a.empid=p.empid)
) B
on A.id=B.ID
join
dbo.FN_SplitSTR(@IDStr,',') fstt
on A.ID=fstt.Col

insert into Kenta_EmpOEEConfig
(EmpID,EmpName,BCCode,EmpGroupNo,MachineGroup,MachineNo,
Flag,BCGroup,CreateDate,UpdateBy,UpdateDate,ShowOEE,ShowMointor,StartDate)

select EmpID,EmpName, case when BCCode='A' then 'B' else 'A' end,
EmpGroupNo,MachineGroup,MachineNo,
Flag,BCGroup,CreateDate,UpdateBy,UpdateDate,ShowOEE,ShowMointor,GETDATE()
from Kenta_EmpOEEConfig p
join dbo.FN_SplitSTR(@IDStr,',') fstt on p.ID=fstt.Col

where p.id=(select max(id) from Kenta_EmpOEEConfig a where a.empid=p.empid)

end
end
GO

select * from Kenta_EmpOEEConfig order by StartDate asc,ID asc
Exec [Kenta_UpandAddEmpOEEConfig] '13,34'

转载于:https://www.cnblogs.com/chengjun/p/4497013.html