How to store more than one value in an "SQL variable"?

Question

I work with a system that has a very limited report. The reports are basically restricted to one sql query.

I need to present in a report a query that, in my query, is being informed by the user a multi-selection parameter, I will explain better:

The user informs about which units want to view the report, imagine that he selects units 1 and 2, then in the clause IN will stay:

Where CODIGO_UNIDADE In (1,2)

So far everything perfect, the problem is that I need to present in the report which units the user informed in the parameters, and, the only way I have to do this is by declaring variable in SQL and capturing the parameter that the user informed:

DECLARE @UNIDADE VARCHAR (100)

Set @UNIDADE = (

Select NOME from UNIDADES

Where CODIGO_UNIDADE In (:UNIDADE_INFORMADA_PELO_USUÁRIO)

)

Select @Unidade As 'Unidade Informada'

As you may already be wondering, this cannot be done, because as the subquery returns more than one value an error is returned:

Mensagem 512, Nível 16, Estado 1, Linha 3
A subconsulta retornou mais de 1 valor. Isso não é permitido quando a subconsulta segue um =, !=, <, <= , >, >= ou quando ela é usada como uma expressão.

What is the easiest way to do this? Considering the limitations (passing the value to a variable) I have in my report?

Answer 1

Hey, buddy!
I believe the easiest way to do this is by using string concatenation. Below is an example applying to your need:

DECLARE @UNIDADE VARCHAR(100) = ''
SELECT @UNIDADE += UNIDADE + ', ' FROM UNIDADES WHERE CODIGO_UNIDADE IN (/* Códigos 
informados*/)

SELECT @UNIDADE AS 'Unidades Selecionadas'

I hope I’ve helped.
Hugs,

Answer 2

You can use the instruction FOR XML:

DECLARE @UNIDADE VARCHAR(100) = ''

set @UNIDADE =
(
    select  NOME + ','
    from    UNIDADES 
    Where   CODIGO_UNIDADE In (:UNIDADE_INFORMADA_PELO_USUÁRIO)
     for xml path('')
)

set @unidade = Left(@unidade, Len(@unidade) - 1)

select @unidade as 'Unidade(s) Informada(s)'

I added the Left to remove the comma.

Answer 3

Otávio, evaluate if Report Builder accepts the following construction:

-- código #1
declare @tbUnidade table (Deno_Unidade ___);

INSERT into @tbUnidade (Deno_Unidade)
  SELECT NOME 
    from UNIDADES
    where CODIGO_UNIDADE in (:parametro); 

The Deno_unit column shall be declared with the same data type as in the NAME column.

The above form is useful if you need to keep separate unit denominations.