T-SQL how to assign a variable inside another variable assignment

Question

I've inherited a stored proc and I need to add and assign a new variable. The piece of the code that's relevant is:

DECLARE @tableRows VARCHAR(MAX) = '';
    SET @tableRows
        = N'
  :: Resource Scheduler'
  + N'
Date: ' + CAST(CONVERT(NVARCHAR, DATENAME(WEEKDAY, @rpt_start_date)) AS VARCHAR(100)) + ' '
          + CAST(CONVERT(NVARCHAR, CAST(@rpt_start_date AS DATE), 100) AS VARCHAR(100)) + ''
          + ''
          + ''
          + '' 
  + ''
  + '' 
  + '' 
  + ''
          + ''
  + ''
  + ''
  + '';

    SELECT @tableRows
        = @tableRows + '' 
  + '' 
  + '' 
  + '' 
  + '' 
  + '' 
  + ''
  + ''
  + ''
          + ''
  + ''
    FROM
            tbl_sched           s WITH (NOLOCK)
        INNER JOIN
            tbl_sched_res_date  srd WITH (NOLOCK)
                ON s.[sched_id] = srd.[sched_id]
        INNER JOIN
            tbl_sched_request  sr WITH (NOLOCK)
                ON s.[sched_id] = sr.[sched_id]
        INNER JOIN
            tbl_user            ru WITH (NOLOCK)
                ON sr.[req_for_user_id] = ru.[user_id]
        INNER JOIN
            tbl_user            hu WITH (NOLOCK)
                ON s.create_by = hu.[user_id]
        INNER JOIN
            tbl_res             r WITH (NOLOCK)
                ON srd.[res_id] = r.[res_id]
        INNER JOIN
            tbl_grp             g WITH (NOLOCK)
                ON r.[grp_id] = g.[grp_id]
        INNER JOIN
            tbl_loc             l WITH (NOLOCK)
                ON g.[loc_id] = l.[loc_id]
        INNER JOIN
            tbl_region          rg WITH (NOLOCK)
                ON l.[region_id] = rg.[region_id]
        LEFT OUTER JOIN -- changed from inner join 
            tbl_sched_udf_val   suv_f WITH (NOLOCK)
                ON suv_f.[sched_id] = s.[sched_id]
AND suv_f.[udf_id] =
   (
   SELECT
   u.[udf_id]
   FROM
   tbl_udf u WITH (NOLOCK)
   WHERE
   u.[udf_desc] LIKE @rs_customtab_food

   )
AND suv_f.[string_value] IS NOT NULL
AND suv_f.[string_value] = 'Yes'
        LEFT OUTER JOIN -- changed from inner join 
            tbl_sched_udf_val   suv_t WITH (NOLOCK)
                ON suv_t.[sched_id] = s.[sched_id]
AND suv_t.[udf_id] =
   (
   SELECT
   u.[udf_id]
   FROM
   tbl_udf u WITH (NOLOCK)
   WHERE
   u.[udf_desc] LIKE @rs_customtab_tech

   )
AND suv_t.[string_value] IS NOT NULL
AND suv_t.[string_value] = 'Yes'
        LEFT OUTER JOIN -- changed from inner join 
            tbl_sched_udf_val   suv_o WITH (NOLOCK)
                ON suv_o.[sched_id] = s.[sched_id]
AND suv_o.[udf_id] =
   (
   SELECT
   u.[udf_id]
   FROM
   tbl_udf u WITH (NOLOCK)
   WHERE
   u.[udf_desc] LIKE @rs_customtab_os

   )
AND suv_o.[string_value] IS NOT NULL
AND suv_o.[string_value] = 'Yes'
        LEFT OUTER JOIN
            tbl_sched_res_setup srs WITH (NOLOCK)
                ON (
                       s.[sched_id] = srs.[sched_id]
                       AND srd.[res_id] = srs.[res_id]
                   )
        LEFT OUTER JOIN
            tbl_setup           su WITH (NOLOCK)
                ON (srs.[setup_id] = su.[setup_id])
    WHERE
            l.[loc_id] = 13-- 1177 Sixth Ave ( ONLY )
            AND s.[deleted_flag] = 0
            AND r.[obsolete_flag] = 0
            AND g.[obsolete_flag] = 0
            AND l.[obsolete_flag] = 0
            AND rg.[obsolete_flag] = 0
            AND srd.[busy_start_date_local] >= CONVERT(NVARCHAR(20), @rpt_start_date, 112)
            AND srd.[busy_start_date_local] < CONVERT(NVARCHAR(20), @rpt_end_date, 112)
    ORDER BY
            srd.[mtg_start_date_local],
            r.[res_hdr];

SELECT @tableRows = @tableRows + 'START TIME END TIME ROOM MEETING TITLE ATTENDEES INVITEE' + CHAR(39) + 'S NAME HOSTS NAMES FOOD SERVICES REQUESTS TECHNOLOGY REQUESTS OFFICE SERVICES REQUESTS
' + CAST(CONVERT(NVARCHAR, CAST(srd.[mtg_start_date_local] AS TIME), 100) AS VARCHAR(100)) + ' ' + CAST(CONVERT(NVARCHAR, CAST(srd.[mtg_end_date_local] AS TIME), 100) AS VARCHAR(100)) + ' ' + CAST(r.[res_hdr] AS VARCHAR(100)) + ' ' + CAST(s.[sched_desc] AS VARCHAR(100)) + ' ' + CAST(s.[num_attendees] AS VARCHAR(100)) + ' ' + CAST(ru.[user_name] AS VARCHAR(100)) + ' ' + CAST(hu.[user_name] AS VARCHAR(100)) + ' ' + CAST(dbo.ufn_rsConcatCustomTabServices(@rs_customtab_food,s.[sched_id]) AS VARCHAR(4000)) + ' ' + CAST(dbo.ufn_rsConcatCustomTabServices(@rs_customtab_tech,s.[sched_id]) AS VARCHAR(4000)) + ' ' + CAST(dbo.ufn_rsConcatCustomTabServices(@rs_customtab_os,s.[sched_id]) AS VARCHAR(4000)) + '';

As you can see, it's a complicated query. @tableRows is used later on to create the body of an email. Now, I need to get s.sched_desc (see line 7 of SELECT statement) and assign it to a second variable, so that I can use it in the Subject line of the same email. I've tried adding

+ (SELECT @sched_desc = SELECT [sched_desc])

to the bottom of the SELECT statement but it's no good (incorrect syntax near parenthesis). I've also tried

 + '' + (SELECT @sched_desc = CAST(s.[sched_desc] AS VARCHAR(100))) + ''

but again it's expecting another parenthesis. I know I can do this by turning this whole thing into a string and then executing it with sp_executesql (see this example) but I'd prefer to avoid dynamic sql if possible. On the other hand, I really don't want to execute this query twice. Is there another way to get around this?

paneerakbari · Accepted Answer

Your stated task would be more easily accomplished and supported by employing a templating language and some basic string interpolation.

It is not possible to set a variable value within the process of setting another variable's value, but you can do so within the same SELECT.

In your query, add a comma after the closing quote and then set your @sched_desc variable:

SELECT @tableRows = @tableRows + '


The second variable assignment has access to the same data as the original query, but it will need to be included in whatever selecting process is used to retrieve the value of @tableRows.
As an additional note, I will strongly advise you to identify alternatives to using NOLOCK - here are some links to get you started on that path:
https://www.brentozar.com/archive/2021/11/nolock-is-bad-and-you-probably-shouldnt-use-it/
https://www.brentozar.com/archive/2018/10/using-nolock-heres-how-youll-get-the-wrong-query-results/
https://www.brentozar.com/archive/2016/12/nolock-ever-right-choice/
https://www.brentozar.com/archive/2021/01/but-surely-nolock-is-okay-if-no-ones-changing-data-right/

T-SQL how to assign a variable inside another variable assignment

Answers (2)

Related Questions