How can I create array in SQL Server?

4

In SQL Server Management Studio 2012 have the possibility to create array in a procedure? I did a google search and did not find, it seems like a table variable is used to store more than one value from a select.

    
asked by anonymous 22.05.2016 / 12:50

2 answers

5

You can try with the example below

select STUFF((select ', ' + descricao from PRODUTOS
              FOR xml PATH (''))
            , 1, 1, '')

STUFF

FOR XML

    
22.05.2016 / 14:07
1

The only way I know it is how you said it, create (User-defined TABLE Type) as follows.

CREATE TYPE [dbo].[tp_IDsTable] AS TABLE(
    [Id] [int] NULL
    -- mais campos
)
GO

create PROCEDURE TestePassandoArray
  @Id int 
 as

DECLARE @IDsTable tp_IDsTable -- aqui você criar um tipo como se fosse um array.  

insert into @IDsTable 
SELECT IdUsuario FROM tb_Usuarios where IdUsuario = @Id;

select * from @IDsTable

GO

EXEC TestePassandoArray 2
    
08.06.2016 / 21:26