A way to make this dynamic SQL

Question

Post reply

A way to make this dynamic SQL

mw_sql_developer

SSCoach

Points: 19445

August 12, 2022 at 5:09 pm

#4075832

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 )

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

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

pietlinden SSC Guru Points: 63482 More actions · Answer 1

pietlinden

SSC Guru

Points: 63482

August 12, 2022 at 6:18 pm

#4075849

use REPLACE()

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 2

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

😎

kaj SSCommitted Points: 1593 More actions · Answer 3

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?

kaj SSCommitted Points: 1593 More actions · Answer 4

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 🙂 )