T-SQL如何在另一個變數賦值中分配一個變數

我繼承了一個存盤程序，我需要添加和分配一個新變數。相關的代碼是：

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>';

如您所見，這是一個復雜的查詢。@tableRows 稍后用于創建電子郵件的正文。現在，我需要獲取 s.sched_desc（參見 SELECT 陳述句的第 7 行）并將其分配給第二個變數，以便我可以在同一封電子郵件的主題行中使用它。我試過添加

  (SELECT @sched_desc = SELECT [sched_desc])

到 SELECT 陳述句的底部，但它不好（括號附近的語法不正確）。我也試過

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

但它再次期待另一個括號。我知道我可以通過將整個事情變成一個字串然后用 sp_executesql 執行它來做到這一點（參見這個例子），但如果可能的話，我寧愿避免使用動態 sql。另一方面，我真的不想執行這個查詢兩次。還有其他方法可以解決這個問題嗎？

uj5u.com熱心網友回復：

通過使用模板語言和一些基本的字串插值，您宣告的任務將更容易完成和支持。

在設定另一個變數值的程序中無法設定變數值，但您可以在同一個SELECT.

在您的查詢中，在結束引號后添加一個逗號，然后設定您的 @sched_desc 變數：

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]
 ...

第二個變數分配可以訪問與原始查詢相同的資料，但它需要包含在用于檢索@tableRows 值的任何選擇程序中。

作為補充說明，我強烈建議您確定使用的替代方案NOLOCK- 這里有一些鏈接可以幫助您開始使用該路徑：

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-changeing-data-right/

uj5u.com熱心網友回復：

Paneerakbari 是正確的，您可以在選擇中分配（并建立）多個變數。這是一個簡化的示例，可以使事情更清楚。

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

結果：

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

為了可讀性和可維護性，我經常發現將復雜的中間計算移到 CROSS APPLY 塊中很有用，然后可以在最終選擇中參考其結果。

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

標籤：

上一篇：TSQL使用列A的值更新列B也正在更新

下一篇：案例運算式-比較日期