时间戳

首页 > 解决方案 > TSQL 在选择语句中更新记录

sql-server - TSQL 在选择语句中更新记录

问题描述

通过 select 语句检索数据集时，我想根据每个检索到的记录的列中的值更新现有记录。我不能使用函数来更新记录，也不能在 select 语句中使用存储过程。我还发现我无法通过从函数执行存储过程来规避这些限制。

有任何想法吗？以下是我当前的代码，其中有一条评论，我觉得我需要调用一些可以更新记录的东西。此代码将在 SSRS 中使用，因此一种解决方案可能是在报告自定义代码中执行存储过程，但我也无法让它工作。

select sw.SMACTVSEQ as JDE_ActiveRec 
    ,sw.SMCSSEQ as JDE_SalesSeq
    ,bu.MCRP04 as JDE_DivID
    ,sw.SMHBMCUS as JDE_CommID
    ,cm.CMDL01 as JDE_CommunityName
    ,rtrim(sw.SMMCU) as JDE_LotID
    ,sw.SMBYR as JDE_BuyerABNo
    ,JDE_SSID = case
        When ab.ABURRF is null then cast(0 as int)
        When ab.ABURRF = '' then cast(0 as int)
        else cast(ab.ABURRF as int)
        end
    ,ss.Customer_ID as SS_CustID
    ,ss.Lot_ID as SS_LotID
    ,ss.Customer_Status as SS_CustStatus
    ,[dbo].[udf_ConvertJDEdate](sw.SMCDJ) as JDE_DateClosed
    ,case when ab.ABURRF >0 then 'Manually Update'
        else
            case when @Update_Mode ='Yes' then
            'Yes/Error' 
            /* **************
               Replace 'Yes/Error' with procedure to update  
               JDE_F0101_ABURRF and return 'Yes' or 'Error'
               ************** */
            else @Update_Mode
            end
        end as Update_Mode
    from [dbo].[crp_F44H501] sw
        left outer join [dbo].[crp_F44H101] cm 
            on cm.[CMHBMCUS]=sw.smhbmcus and cm.[CMCPHASE]=sw.[SMCPHASE] 
        left outer join [dbo].[crp_F0006] bu
            on bu.mcmcu = sw.smmcu 
        left outer join [dbo].[stg_F0101] ab
            on ab.aban8 = sw.SMBYR
        left outer join (
            select distinct(lot_id)
                ,customer_ID
                ,customer_status
            from [dbo].[SS_FactDemographic]
                        ) ss
            on ltrim(ss.lot_id) = ltrim(sw.SMMCU)

    Where sw.smactvseq='1' 
        and sw.SMBYR > 0
        and ab.ABURRF <> ss.Customer_ID
        and ss.Customer_Status = 'Buyer'
        and (bu.MCRP04 = @Division_ID or @Division_ID ='All')
        and (ltrim(sw.SMHBMCUS) = @Community_ID or @Community_ID ='All')
    Order by JDE_DivID,JDE_CommID,JDE_LotID

标签： sql-servertsqlssrs-2012

解决方案

将 UPDATE 查询放在报表查询的 TSQL 中，放在数据查询之前。只要您的查询有效并且您有权限，SSRS 就会运行 TSQL 来更新数据、使用 TEMP 表等。SSRS 查询编辑器可能不喜欢您可以做的所有事情，因此您可能需要在 SSMS 中进行查询（其中我通常都会这样做）。

我认为您的查询看起来像：

UPDATE sw
SET Update_Mode = CASE WHEN ab.ABURRF >0 THEN 'Manually Update'
                        ELSE CASE WHEN @Update_Mode ='Yes' THEN 'Yes/Error' ELSE @Update_Mode END
                        END
FROM [dbo].[crp_F44H501] sw  
INNER JOIN [dbo].[stg_F0101] ab on ab.aban8 = sw.SMBYR


select sw.SMACTVSEQ as JDE_ActiveRec 
    ,sw.SMCSSEQ as JDE_SalesSeq
    ,bu.MCRP04 as JDE_DivID
    ,sw.SMHBMCUS as JDE_CommID
    ,cm.CMDL01 as JDE_CommunityName
    ,rtrim(sw.SMMCU) as JDE_LotID
    ,sw.SMBYR as JDE_BuyerABNo
    ,JDE_SSID = case
        When ab.ABURRF is null then cast(0 as int)
        When ab.ABURRF = '' then cast(0 as int)
        else cast(ab.ABURRF as int)
        end
    ,ss.Customer_ID as SS_CustID
    ,ss.Lot_ID as SS_LotID
    ,ss.Customer_Status as SS_CustStatus
    ,[dbo].[udf_ConvertJDEdate](sw.SMCDJ) as JDE_DateClosed
    ,sw.Update_Mode
    from [dbo].[crp_F44H501] sw
        left outer join [dbo].[crp_F44H101] cm 
            on cm.[CMHBMCUS]=sw.smhbmcus and cm.[CMCPHASE]=sw.[SMCPHASE] 
        left outer join [dbo].[crp_F0006] bu
            on bu.mcmcu = sw.smmcu 
        left outer join [dbo].[stg_F0101] ab
            on ab.aban8 = sw.SMBYR
        left outer join (
            select distinct(lot_id)
                ,customer_ID
                ,customer_status
            from [dbo].[SS_FactDemographic]
                        ) ss
            on ltrim(ss.lot_id) = ltrim(sw.SMMCU)
    Where sw.smactvseq='1' 
        and sw.SMBYR > 0
        and ab.ABURRF <> ss.Customer_ID
        and ss.Customer_Status = 'Buyer'
        and (bu.MCRP04 = @Division_ID or @Division_ID ='All')
        and (ltrim(sw.SMHBMCUS) = @Community_ID or @Community_ID ='All')
    Order by JDE_DivID,JDE_CommID,JDE_LotID

推荐阅读