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'<tr>
<bgcolor="#6081A0" style="FONT-FAMILY:arial,san-serif;FONT-WEIGHT:normal;color:#6081A0">  :: Resource Scheduler</tr>'
  + N'<tr>
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)) + '</tr>'
          + '<table border="1" width="100%">'
          + '<tr bgcolor="#DC5E3F" style="FONT-FAMILY:arial,san-serif;FONT-WEIGHT:bold;color:white">'
          + '<td style="text-align:center;vertical-align:middle">START TIME</td>' 
  + '<td style="text-align:center;vertical-align:middle">END TIME</td>'
  + '<td style="text-align:center;vertical-align:middle">ROOM</td>' 
  + '<td>MEETING TITLE</td>' 
  + '<td style="text-align:center;vertical-align:middle">ATTENDEES</td>'
          + '<td>INVITEE' + CHAR(39) + 'S NAME</td><td>HOSTS NAMES</td>'
  + '<td>FOOD SERVICES REQUESTS</td>'
  + '<td>TECHNOLOGY REQUESTS</td>'
  + '<td>OFFICE SERVICES REQUESTS</td></tr>';

    SELECT @tableRows
        = @tableRows + '<tr ' + 'bgcolor=' +
  + IIF(ROW_NUMBER() OVER (ORDER BY s.[sched_id] DESC) % 2 = 0, '"lightgrey', '"white') + '">' 
  + '<td style="text-align:center;vertical-align:middle">' + CAST(CONVERT(NVARCHAR, CAST(srd.[mtg_start_date_local] AS TIME), 100) AS VARCHAR(100)) + '</td>' 
  + '<td style="text-align:center;vertical-align:middle">' + CAST(CONVERT(NVARCHAR, CAST(srd.[mtg_end_date_local] AS TIME), 100) AS VARCHAR(100)) + '</td>' 
  + '<td style="text-align:center;vertical-align:middle">' + CAST(r.[res_hdr] AS VARCHAR(100)) + '</td>' 
  + '<td>' + CAST(s.[sched_desc] AS VARCHAR(100)) + '</td>' 
  + '<td style="text-align:center;vertical-align:middle">' + CAST(s.[num_attendees] AS VARCHAR(100)) + '</td>' 
  + '<td>' + CAST(ru.[user_name] AS VARCHAR(100)) + '</td>'
  + '<td>' + CAST(hu.[user_name] AS VARCHAR(100)) + '</td>'
  + '<td>' + CAST(dbo.ufn_rsConcatCustomTabServices(@rs_customtab_food,s.[sched_id]) AS VARCHAR(4000)) + '</td>'
          + '<td>' + CAST(dbo.ufn_rsConcatCustomTabServices(@rs_customtab_tech,s.[sched_id]) AS VARCHAR(4000)) + '</td>'
  + '<td>' + CAST(dbo.ufn_rsConcatCustomTabServices(@rs_customtab_os,s.[sched_id]) AS VARCHAR(4000)) + '</td></tr>'
    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 + '</table>';

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

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

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?

That's a large dollop of code, perhaps consider a Minimal, Reproducible Example? — Stu
– Stu, Commented Jan 12, 2022 at 21:26
Stu raises some good points, but my advice is to re-think this approach and migrate away from doing all of this in one big string concatenation. You could use OUTPUT parameters on a stored proc and pass those values to a templating language. It will be far more flexible and long-term maintainable, and it will enable you to set multiple content areas in your resulting message (and even re-use the same proc for multiple message media). — paneerakbari
– paneerakbari, Commented Jan 12, 2022 at 21:37
You can't set a variable value inside of another variable assignment. but you can do it within the same SELECT by adding a comma and the intended code. — paneerakbari
– paneerakbari, Commented Jan 12, 2022 at 21:42
If you need to spam that many NOLOCK hints in your queries, you have some serious concurrency issues. — Thom A
– Thom A ♦, Commented Jan 12, 2022 at 21:50

paneerakbari · Accepted Answer · 2022-01-13 18:05:05Z

2

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 + '<tr ' + 'bgcolor=' ... </td></tr>',
       @sched_desc = [sched_desc]
  FROM tbl_sched s WITH (NOLOCK)
 INNER JOIN tbl_sched_res_date  srd WITH (NOLOCK)
    ON s.[sched_id] = srd.[sched_id]
 ...

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/

edited Jan 13, 2022 at 18:05

answered Jan 12, 2022 at 21:51

paneerakbari

7451 gold badge5 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Melanie Over a year ago

Thank you so much. This should've been obvious to me, but I really appreciate your help. The original query comes from a software vendor, so I'm loathe to change their code, but I'll examine all the links you sent on NOLOCK and bring it up to them. Thanks again.

T N · Accepted Answer · 2022-01-12 23:03:06Z

Paneerakbari is correct that you can assign (and build up) more than one variable in the select. Here is a simplified example that may make things clearer.

DECLARE @TableRows VARCHAR(MAX) = '<table>'
DECLARE @Subject VARCHAR(MAX) = '' -- Only the last value is retained here

SELECT
    @TableRows = @TableRows + '<tr><td>' + A.Info + '</td></tr>',
    @Subject = A.Title
FROM (
    VALUES
        (1, 'This', 'This stuff'),
        (2, 'That', 'That stuff'),
        (3, 'More', 'More stuff')
) A(ID, Title, Info)
ORDER BY A.ID

SET @TableRows = @TableRows + '</table>'

SELECT @Subject, @TableRows

Result:

@Subject = 'More'
@TableRows = '<table><tr><td>This stuff</td></tr><tr><td>That stuff</td></tr><tr><td>More stuff</td></tr></table>'

For readability and maintainability, I often find it useful to move complex intermediate calculations into a CROSS APPLY block, the results of which can then be referenced in the final select.

DECLARE @TableRows VARCHAR(MAX) = '<table>'
DECLARE @Subject VARCHAR(MAX) = '' -- Only the last value is retained here

SELECT
    @TableRows = @TableRows + R.ComplexRowConstruction,
    @Subject = A.Title
FROM (
    VALUES
        (1, 'This', 'This stuff'),
        (2, 'That', 'That stuff'),
        (3, 'More', 'More stuff')
) A(ID, Title, Info)
CROSS APPLY (
    SELECT ComplexRowConstruction =
        '<tr>'
        + '<td>' + A.Info + '</td>'
        + '</tr>'
) R
ORDER BY A.ID

SET @TableRows = @TableRows + '</table>'

SELECT @Subject, @TableRows

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related