January 12, 2022 at 9:18 pm
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?
January 12, 2022 at 10:18 pm
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
January 13, 2022 at 5:40 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy