I have a block of code that I want to use over and over in a stored procedure. This block of code will set a number of variables to blank ( '' ). I would like to do this without having to copy/paste the entire code every time.

Long story short, I'm using one stored procedure to send many unique emails with various tables and formats etc.

I'm accomplishing this by declaring these variables at the start of the stored procedure:

/* declare HTML variables */
declare @htmlSTART nvarchar(max), @htmlEND nvarchar(max), @body nvarchar(max), @greeting nvarchar(max), @closing nvarchar(max), @subject nvarchar(max)
declare @recipients nvarchar(max), @profile_name nvarchar(max), @body_format nvarchar(max), @copy_recipients nvarchar(max), @blind_copy_recipients nvarchar(max)
declare @table_header_01 nvarchar(max), @paragragh_01 nvarchar(max), @HTML_table_01 nvarchar(MAX), @disclaimer_01 nvarchar(max)
declare @table_header_02 nvarchar(max), @paragragh_02 nvarchar(max), @HTML_table_02 nvarchar(MAX), @disclaimer_02 nvarchar(max)
declare @table_header_03 nvarchar(max), @paragragh_03 nvarchar(max), @HTML_table_03 nvarchar(MAX), @disclaimer_03 nvarchar(max)
declare @table_header_04 nvarchar(max), @paragragh_04 nvarchar(max), @HTML_table_04 nvarchar(MAX), @disclaimer_04 nvarchar(max)

The stored procedure then queries a bunch of data, creates a temporary master data table for the emails, recursively pulls each unique email address with a cursor, creates tables with html, and emails relevant data like so:

set @body =
    concat
        (
            @htmlSTART
            , @greeting
            , @paragragh_01, @table_header_01, @HTML_table_01
            , @paragragh_02, @table_header_02, @HTML_table_02
            , @paragragh_03, @table_header_03, @HTML_table_03
            , @paragragh_04, @table_header_04, @HTML_table_04
            , @closing
            , @disclaimer_01
            , @htmlEND
        )
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile_name
    , @body = @body
    , @body_format = @body_format
    , @recipients = @recipients
    , @copy_recipients = @copy_recipients
    , @blind_copy_recipients = @blind_copy_recipients
    , @subject = @subject

At the start of each email section I am setting the variables to blank:

set @table_header_01 = '' set @paragragh_01 = '' set @HTML_table_01 = '' set @disclaimer_01 = ''
set @table_header_02 = '' set @paragragh_02 = '' set @HTML_table_02 = '' set @disclaimer_02 = ''
set @table_header_03 = '' set @paragragh_03 = '' set @HTML_table_03 = '' set @disclaimer_03 = ''
set @table_header_04 = '' set @paragragh_04 = '' set @HTML_table_04 = '' set @disclaimer_04 = ''

Rather than having to remember which tables etc to remove from set @body = for each email, I reset the variables each time because some of the emails will contain only one table (or none) and some will contain multiple tables. If the @html_table variables are blank it will not mess up the email, but removing the wrong ones from set @body = will mess it up when I EXEC msdb.dbo.sp_send_dbmail .

I found THIS thread which talks about creating temp procedures and I tried dynamic SQL as well, but the dynamic SQL cannot set the variables from the outer scope as far as I can tell. It throws the error stating that the variables @HTML_table_01 etc have not been declared.

Is there any way to repeatedly set all the variables to '' without copy/pasting/typing it out each time? If there's a good method for this, I'm sure there's a multitude of other applications for said method other than my specific use case.