1

The problem is as follows: I have some parameters

Declare @testParam nvarchar(max) = '',
Declare @ID int = 3

and I have many complex tables and all of them have a column that is named differently, but resembles some kind of status that is of type BIT.

ID Status some other values...
1 0 ...
2 1 ...
3 1 ...
4 1 ...

I want to expend text of @testParam every time the Status value = 1 for the record matching my ID. The text I need to add to this @testParam will be different for every table.

As for example:

select @testParam = @testParam + 'Full capacity ' 
from CapacityTable
where CapacityStatus= 1 and ID = @ID

select @testParam = @testParam + 'Not enough coal ' 
from CoalTable
where LackOfCoal = 1 and ID = @ID

select @testParam = @testParam + 'Too low temperature' 
from OvenTable 
where OvenStatus = 1 and ID = @ID

select @testParam as Result

If all of the select statments meet the conditions, the result that I expect would look like:

Result
Full capacity Not enough coal Too low temperature

I know I could achieve this by using many IF statements, but I need this query to be as optimal as possible. Unless using IF statements doesn't change much in case of execution time I'm looking for an alternate solution.

igy234
  • 59
  • 1
  • 7
  • Don't really understand what you are asking here, can you please add some example data and the output you are trying to achieve? – iamdave Feb 22 '21 at 11:37
  • @iamdave added what you've asked for :) – igy234 Feb 22 '21 at 12:18
  • Seems to me you should be looking for a solution that works correctly FIRST before you try to "optimize it" to achieve some undefined objective. String aggregation is a common topic - TSQL has [string_agg()](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) in current versions - alternatives exist and have been posted many times for older versions. – SMor Feb 22 '21 at 13:00
  • And perhaps adding a computed column or other conversion technique to associated a numeric value to a string description is a good idea as well. You don't want to keep writing these same descriptions in every query that needs to reference them. – SMor Feb 22 '21 at 13:00
  • Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) See also https://stackoverflow.com/questions/66317406/how-to-view-the-variable-value-in-sql-server/66317595#66317595 for why not to use the syntax you have – Charlieface Feb 22 '21 at 14:42

1 Answers1

0

I have found one way acomplish it by using case clause, but again if there is a better way (in case of performance) I would like to know about it.

Solution using case clause:

select @testParam = @testParam + 
case 
   when CapacityStatus = 1
   then 'Full capacity ' 
   else ''
end
from CapacityTable
where ID = @ID

Apply code above for every table you need in analogical way.

igy234
  • 59
  • 1
  • 7