What would you suggest them to use?
Something which could...

Use sp_executesql:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

This should do:
INSERT INTO msdb.dbo.sysjobhistory_bkp
SELECT * FROM msdb.dbo.sysjobhistory
WHERE run_date < @datepart
AND run_date > (SELECT TOP 1 run_date
        FROM msdb.dbo.sysjobhistory_bkp ORDER BY run_date DESC...

Main question - what is the clustered index on the main table?

You have element-centric XML, you have to use parameter FLAG=2 in OPENXML.

Apart from that - you must use names of elements, not variables.
Variables here can be found only...

You removed this part:

CASE
when sub4.COMMENT_DATE >= DATEADD(DD, -1, GETDATE()) then 'false'

Because your WHERE clauses...

I wonder - what would be those "given situations"?

Posting SQL code has several issues:
1) If you place IFC first and then paste the code the opening {code} disappears
2) Drop-down placement 

January 25, 2017 at 4:39 pm

#1924987

Try not to use DISTINCT.
Ever.
Just forget this keyword exists 

As for this query - keep in mind, that "FOR XML" query is a separate correlated subquery, grouping...

I'm currently thinking of converting a TVF to a stored procedure.
There are complications, especially in terms of usability, but i think it worth to try.

Functions execute code...