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>
</tr>'
<bgcolor="#6081A0" style="FONT-FAMILY:arial,san-serif;FONT-WEIGHT:normal;color:#6081A0"> :: Resource Scheduler
+ N'<tr>Date: ' + CAST(CONVERT(NVARCHAR, DATENAME(WEEKDAY, @rpt_start_date)) AS VARCHAR(100)) + ' '
</tr>'
+ CAST(CONVERT(NVARCHAR, CAST(@rpt_start_date AS DATE), 100) AS VARCHAR(100)) + '
+ '<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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply