Assigning a variable value inside another variable assignment

Question

Post reply

Assigning a variable value inside another variable assignment

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

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

ratbak Default port Points: 1423 More actions · Answer 1

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

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 2

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!