DECLARE @DateString nvarchar(100)='Wed Jun 29 15:57:45 2019'
SELECT CONVERT(datetime,SUBSTRING(@DateString,5,100))

I would change the WHERE in the last query to:

  FROM staging.sfSurveillanceRecordHeader s1
 WHERE NOT EXISTS (SELECT *
        ...

So yesterday we got a bit of good/bad news...

Good, in that the wife has been called back to work and goes back on Tuesday.

Bad, in that the...

So yesterday we got a bit of good/bad news...

Good, in that the wife has been called back to work and goes back on Tuesday.

Bad, in that the wife got...

If output_response_xml =''

then the expression:

LEN(output_response_xml) - LEN(LEFT(output_response_xml,

CHARINDEX ('>Thank you', output_response_xml))) - LEN(RIGHT(output_response_xml, LEN(output_response_xml) - CHARINDEX ('', output_response_xml))) - 1

Is equal to -1, which will give you the error you have.

DELETE tp
  FROM TESTPO tp
 WHERE STATUS = 1
   AND EXISTS(SELECT * 
           ...

If they are different collations then you will have...

I didn't mean that as a slam.  It's just not possible to post general guidlines because everyone's stuff is different.  I just watched the following YouTube on the subject......

It would be helpful if you knew which particular statements within the stored procedure are the long running ones. The first thing to do is identify which statement(s) are taking...

Really you should have the databases set to the same collation as the tempDB as if you create a temporary table you will have problems joining it to your database...

I think the most obvious way to rewrite that query would be:

SELECT u.UserId
  FROM UserTable u 
 WHERE EXISTS(SELECT * 
     ...

;WITH myTable AS
(
    SELECT *
      FROM (VALUES (1,10,'Deny','Denied','a','04/05/2020'),
           ...

An FK is a constraint that enforces the values in the column(s) are present in the primary-key/unique-index of the related table.

It allows you to create more general purpose flexible SQL statement because the full text of the SQL statements may be unknown at compilation

Rather than 'compilation', I'd suggest 'time...

An alternative is like below.  Which performs better depends on total data volume and index(es) present:

Edit: I'm at work so I have only a limited time to review...