String.Format function in VBA and VBScript

Public Function StringFormat(wFormat As String, ParamArray args()) As String
    
    Dim res As String
    Dim x As Long
    
    res = wFormat
    
    res = Replace(res, "\n", vbNewLine)
    res = Replace(res, "\t", vbTab)
    res = Replace(res, "\q", Chr(34))
    res = Replace(res, "\b", "•")    ' \b = Bullet
    
    For x = 0 To UBound(args)
        res = Replace(res, "{" & x & "}", Nz(args(x)))
    Next
    
    StringFormat = res
    
End Function

Usage example:

Debug.Print StringFormat("Hello {0}, it's {1}", "World", Now)

Returns:

Hello World, it's 22/02/2018 18:01:07

---

VBScript version, since VBScript doesn't allow ParamArray variables.
Note: "args" parameter could be a String or an Array()

Function StringFormat(wFormat, args)

    Dim res
    Dim x

    res = wFormat

    res = Replace(res, "\n", vbNewLine)
    res = Replace(res, "\t", vbTab)
    res = Replace(res, "\q", Chr(34))
    res = Replace(res, "\b", "•")    ' \b = Bullet

    if IsArray(args) Then
        For x = LBound(args, 1) To UBound(args, 1)
            res = Replace(res, "{" & x & "}", args(x))
        Next

    Else
        res = Replace(res, "{0}", args)

    End If

    StringFormat = res

End Function

Usage example:

wscript.echo StringFormat("Hello {0}", "World")
wscript.echo StringFormat("Hello {0}, it's {1}", Array("World", Now))

Returns:

Hello World
Hello World, it's 29/03/2018 11:58:54

Notifica Job Falliti / sys_NotifyFailedJob_sp

CREATE PROCEDURE [dbo].[sys_NotifyFailedJob_sp]
(
   @recipients AS nvarchar(MAX)
 , @subjectPrefix AS nvarchar(100) = NULL
)
AS
BEGIN
 --##2018-02-14 - Boso -        sysjobsteps  // @body
 SET NOCOUNT ON

 DECLARE @JobID AS uniqueidentifier
 DECLARE @JobName AS nvarchar(255)
 DECLARE @subject AS nvarchar(255)
 DECLARE @body AS nvarchar(MAX) = ''
  

 DECLARE #JOBS CURSOR FOR
 SELECT job_id AS j
 FROM msdb..sysjobs j
 WHERE j.enabled <> 0


 OPEN #JOBS

 FETCH NEXT 
 FROM #JOBS 
 INTO @JOBID

 WHILE @@FETCH_STATUS = 0
  BEGIN
   DECLARE @PrevInstance AS int
   DECLARE @ErrCount AS int

   
   -- CERCA LA FINE DELLA History DELL'ULTIMA ESECUZIONE DEL JOB
   SELECT TOP 1 @PrevInstance = sjh.instance_id
   FROM msdb..sysjobhistory sjh WITH (NOLOCK)
   WHERE sjh.job_id = @JobID AND 
     sjh.step_id = 0    --> 0 = (Job outcome)
   ORDER BY
     sjh.instance_id DESC
     

   SELECT @PrevInstance = ISNULL(@PrevInstance, 0)


   -- CERCA PER I JOB ANDATI IN ERRORE
   SELECT @ErrCount = COUNT(*)
   FROM msdb..sysjobhistory sjh WITH (NOLOCK)
   WHERE sjh.job_id = @JobID AND 
     sjh.instance_id >= @PrevInstance AND 
     sjh.step_id = 0 AND   --> 0 = (Job outcome)
     sjh.run_status = 0
   


   -- CERCA SE UNO STEP DEL JOB E' ANDATO IN ERRORE
   SELECT @ErrCount = COUNT(*)
   FROM msdb..sysjobsteps s
   WHERE s.job_id = @JobID
    AND s.last_run_outcome = 0



   SELECT @ErrCount = ISNULL(@ErrCount, 0)


   -- MANDA LA MAIL
   IF @ErrCount > 0
    BEGIN
     SELECT   @jobName = j.name
       , @subject = LTRIM(ISNULL(@subjectPrefix, '') + ' Job "' + j.name + '" terminato CON ERRORI!')
     FROM msdb..sysjobs AS j WITH (NOLOCK)
     WHERE j.job_id = @JobID 



     -- COMPONE IL BODY DELLA MAIL
     SET @body = N'Job name:   ' + @JobName


     SELECT TOP 1 
       @body += N'
Step name:  ' + x.step_name + '

Message:
--------
' + x.message
     FROM msdb..sysjobhistory x
     WHERE x.run_status = 0
      AND x.job_id = @JobID
     ORDER BY 
       x.run_date DESC, x.run_time DESC



     SELECT TOP 1 
       @body += N'

Subsystem:  ' + s.subsystem + '
Command:    ' + s.command
   FROM msdb..sysjobsteps s
   WHERE s.job_id = @JobID
    AND s.last_run_outcome = 0
   ORDER BY 
     s.last_run_date DESC, s.last_run_time DESC



     EXEC msdb..sp_send_dbmail
        @recipients = @recipients
      , @subject =  @subject
      , @body_format =  'TEXT'
      , @body =  @body
    END
   
   
   FETCH NEXT 
   FROM #JOBS 
   INTO @JOBID
  END

 CLOSE #JOBS
 DEALLOCATE #JOBS

END
GO