A way to make this dynamic SQL

  •  

    Team:

    Is there an easy way to squeez this sql statement to a varchar(max) variable ?

    I hate to be going through each single quote and making it double .. bla bla

    Declare @sql VARCHARN(MAX)

     

    --Something like below

    @sql =  '

    Select * from x where w.animal in ( 'rabbit', 'cow', 'bird' )

     

    '

     

     

    ;
    With a as
    (
    Select
    x.ClaimType + x.FormNbr as ClaimID
    from
    Reference.ICO_MMP.EQI_EncounterData_Master_Summary_tmp X
    WHERE
    Units in ( 'Procedures' )
    GROUP BY x.ClaimType + x.FormNbr having sum(MMS_PAID) > 0.00
    )
    ,
    b as
    (
    Select ID,
    CASE
    WHEN x.MMS_PAID > 0 THEN 1
    WHEN x.MMS_PAID < 0 THEN -1
    WHEN x.MMS_PAID = 0.00 THEN 0
    END as Utilization
    FROM
    Reference.ICO_MMP.EQI_EncounterData_Master_Summary_tmp x
    INNER JOIN a on ( a.ClaimID = x.ClaimType + x.FormNbr )
    WHERE
    Units in ( 'Procedures' )
    )
    UPDATE T
    SET
    Utilization = X.Utilization
    FROM
    (
    Select ID, Utilization
    FROM
    b
    )X
    INNER JOIN Reference.ICO_MMP.EQI_EncounterData_Master_Summary_tmp T
    ON ( T.ID = X.ID )

     

     

     

     

  • use REPLACE()

  • Just to understand your question; why would you like to do this?

    😎

     

  • What is the relationship between the first part about putting a query into a declared variable and the published formatted query? I'm confused.

    The only part of the update query that would fit into the "rabbit, cow, bird" analogy would be the Units column. Is that what you want to parameterize?

     

  • If I have interpreted your question correctly, you could try something like this:

    DECLARE @SQL NVARCHAR(MAX) = N'
    WITH util AS (
    SELECT
    x.*,
    CASE
    WHEN x.MMS_PAID > 0 THEN 1
    WHEN x.MMS_PAID < 0 THEN -1
    WHEN x.MMS_PAID = 0.00 THEN 0
    END AS UtilizationX,
    SUM(MMS_PAID) over(partition by x.ClaimType, x.FormNbr, x.Units) AS sumMMS_PAID
    FROM
    ICO_MMP.EQI_EncounterData_Master_Summary_tmp x
    )
    UPDATE util
    SET Utilization = UtilizationX
    WHERE Units IN ( SELECT CAST(value AS VARCHAR(50)) FROM STRING_SPLIT(@pUnits,'','') )
    AND sumMMS_PAID > 0;'

    /* Calling the dynamic update statement */
    DECLARE @pUnitList VARCHAR(500) = 'rabbit,cow,bird'
    EXECUTE dbo.sp_executesql @stmt = @SQL, @params = N'@pUnits VARCHAR(500)', @pUnits = @pUnitList

    or just
    EXECUTE dbo.sp_executesql @stmt = @SQL, @params = N'@pUnits VARCHAR(500)', @pUnits = 'rabbit,cow,bird'

    (and yes, I have tinkered with your update statement in my example 🙂 )

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply