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

Navigation
2

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

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

The user informs about which units he wants to view the report, suppose he selects units 1 and 2, then in the IN clause: 

Where CODIGO_UNIDADE In (1,2)

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

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 can not 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 (pass value to a variable) that I have in my report?

    
asked by anonymous 29.11.2018 / 01:04

3 answers

2

You can use the FOR XML statement: 

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.

    
29.11.2018 / 14:01
2

Hello, my friend! Home I believe the easiest way to do this is by using string concatenation. Here 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 have helped. Hugs,

    
29.11.2018 / 02:29
1

Otavio, evaluate whether Report Builder supports the following build: 

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

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

The Deno_Unidade column should be declared with the same data type as the NAME column.

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

    
29.11.2018 / 13:32
Entering an arithmetic expression instead of an integer in C Format real (br) value for decimal (10,2) of mysql