使用 WITH 结果执行两个语句

Question

我需要根据 WITH 数据集的结果执行两个不同的语句。但是文档说，WITH 只能跟一个指令。

有没有一种简单的方法可以使用该 WITH 返回的数据集执行两个语句？我在想一些事情，比如在开头包含我的两个陈述......结束部分，或括号，...

这是我的代码，我想执行一个更新和一个插入，结果为 WITH ：

with EMPRESAS
as (
select splitdata as EMPRESA from fnSplitString(@EMPRESES_CONTA_SEPARADES_PER_COMA, ',')
union
select substring(@BRANCH_1,1,1)
), PROA_EMPRESAS
as (
select case EMPRESA when 'A' then 2 when 'B' then 5 when 'C' then 4 when 'D' then 1 when 'E' then 3 end as PROA_EMPRESA
from EMPRESAS
)
update CLIENTE_CLIENTE_TIPO set CLG_COD = dbo.cCLIENTE_GRUPO_ClienteConActividad(), CCE_COD = @TARGMAG, CCT_ORI = 2
from PROA_EMPRESAS
inner join CLIENTE_CLIENTE_TIPO on CTI_COD = dbo.cCLIENTE_TIPO_Cliente() and CLI_COD = @CLI_COD and EMP_KEY = PROA_EMPRESA 
where isnull(CLG_COD,-1) <> dbo.cCLIENTE_GRUPO_ClienteConActividad() or isnull(CCE_COD,'') <> @TARGMAG or isnull(CCT_ORI,-1) <> 2;
insert into CLIENTE_CLIENTE_TIPO (CTI_COD, CLI_COD, EMP_KEY, CLG_COD, CCE_COD, CCT_ORI)
select dbo.cCLIENTE_TIPO_Cliente(), @CLI_COD, PROA_EMPRESA, dbo.cCLIENTE_GRUPO_ClienteConActividad(), @TARGMAG, 2
from PROA_EMPRESAS
left outer join CLIENTE_CLIENTE_TIPO EXISTE on EXISTE.CTI_COD = dbo.cCLIENTE_TIPO_Cliente() and EXISTE.CLI_COD = @CLI_COD and EXISTE.EMP_KEY = PROA_EMPRESA
where PROA_EMPRESA is not null and EXISTE.CLI_COD is null;

有没有一种简单的方法可以将我的 INSERT 和 UPDATE 作为单个指令，或者我真的需要为每个指令创建一个存储过程或重新定义我的 WITH？

谢谢。

Answer 1

您有两种可能性：

1( 使用临时表具体化中间结果集：

with EMPRESAS
as (
select splitdata as EMPRESA from fnSplitString(@EMPRESES_CONTA_SEPARADES_PER_COMA, ',')
union
select substring(@BRANCH_1,1,1)
)
select case EMPRESA when 'A' then 2 when 'B' then 5 when 'C' then 4 when 'D' then 1 when 'E' then 3 end as PROA_EMPRESA
INTO #temp
from EMPRESAS;
update CLIENTE_CLIENTE_TIPO set CLG_COD = dbo.cCLIENTE_GRUPO_ClienteConActividad(), CCE_COD = @TARGMAG, CCT_ORI = 2
from #temp PROA_EMPRESAS
...
INSERT INTO ...
select dbo.cCLIENTE_TIPO_Cliente(), @CLI_COD, PROA_EMPRESA, dbo.cCLIENTE_GRUPO_ClienteConActividad(), @TARGMAG, 2
from #temp PROA_EMPRESAS ...

2( 使用WITH和MERGE语句：

with EMPRESAS
as (
select splitdata as EMPRESA from fnSplitString(@EMPRESES_CONTA_SEPARADES_PER_COMA, ',')
union
select substring(@BRANCH_1,1,1)
), PROA_EMPRESAS
as (
select case EMPRESA when 'A' then 2 when 'B' then 5 when 'C' then 4 when 'D' then 1 when 'E' then 3 end as PROA_EMPRESA
from EMPRESAS
)
MERGE CLIENTE_CLIENTE_TIPO AS trg
USING AS src
ON src.... = trg....
WHEN MATCHED AND add_condition... THEN
UPDATE ...
WHEN NOT MATCHED BY TARGET THEN
INSERT

Answer 2

merge 语句非常适合这种情况。我尝试实现类似的逻辑，但可能是错误的，因为如果没有示例数据和架构详细信息，很难可视化场景。您可以参考 MSDN 文档 这里 和 这里

以下是示例解决方案：

;WITH EMPRESAS AS 
(
SELECT  splitdata as EMPRESA 
FROM    fnSplitString(@EMPRESES_CONTA_SEPARADES_PER_COMA, ',')
UNION
SELECT  substring(@BRANCH_1,1,1)
), 
PROA_EMPRESAS AS 
(
SELECT  CASE EMPRESA WHEN 'A' THEN 2 WHEN 'B' THEN 5 WHEN 'C' WHEN 4 when 'D' WHEN 1 when 'E' THEN 3 END AS PROA_EMPRESA
FROM    EMPRESAS
)
MERGE   CLIENTE_CLIENTE_TIPO AS T
USING   PROA_EMPRESAS AS S
ON      CTI_COD = dbo.cCLIENTE_TIPO_Cliente() 
AND CLI_COD = @CLI_COD 
AND EMP_KEY = S.PROA_EMPRESA
WHEN    MATCHED
THEN    UPDATE 
SET CLG_COD = dbo.cCLIENTE_GRUPO_ClienteConActividad()
, CCE_COD = @TARGMAG
, CCT_ORI = 2
WHEN    NOT MATCHED BY T 
THEN    INSERT INTO CLIENTE_CLIENTE_TIPO (CTI_COD, CLI_COD, EMP_KEY, CLG_COD, CCE_COD, CCT_ORI)
VALUES(@CLI_COD, S.PROA_EMPRESA, dbo.cCLIENTE_GRUPO_ClienteConActividad(), @TARGMAG, 2)