Assigning a variable value inside another variable assignment

  • Hi - 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?

  • Possibly.

    Is sched_desc going to be the same for every row? Do you want a concatenated list or a single value?

    You're using the SQL Server trick of concatenating multiple rows in a select. You could select

        @sched_desc = s.sched_desc,
    @tableRows = @tableRows + ...

    But that will simply return the last sched_desc value.

    You could concatenate them if that is the intent -- e.g., for comma-delimited, something like :

    DECLARE @sched_desc = varchar(max);
    SELECT
    @sched_desc = @sched_desc + s.sched_desc + ',',
    @table_Rows = @table_Rows + ....

    (w/ something at the end to trim trailing comma)

    Other feedback:

    You can eliminate a bit of redundancy. If a where clause says that a column must equal a constant, you do not also need to say that the column is not null -- that's inherent in it equaling a value (NULL is never equal to anything, even NULL) -- e.g.,

    suv_f.[string_value] IS NOT NULL AND 
    suv_f.[string_value] = 'Yes'

    should just be

    suv_f.[string_value] = 'Yes'

    I would also be tempted to extract the subqueries into separate queries that populate variables , & then use those variables in your query to simplify the query a bit -- e.g.,

    DECLARE @rs_customtab_food_id int = (
    SELECT
    u.[udf_id]
    FROM
    tbl_udf u WITH (NOLOCK)
    WHERE
    u.[udf_desc] LIKE @rs_customtab_food
    );

    .
    .
    .
    .
    suv_f.[udf_id] = @rs_customtab_food_id

     

  • Thank you very much for your feedback. You've given me a couple of useful options, plus a couple of things to think about. This is a query that came from the software vendor, so I'm loathe to change it much more than I have to, but I will think about your advice. Very helpful!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply