Can you create an array in SQL Server?

Question

In SQL Server Management Studio 2012 you have the possibility to create an array in a trial? I did a google search and did not find, apparently it is used such a table variable to store more than one value of a select.

Answer 1

You can try with the example below

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

STUFF

FOR XML

Answer 2

The only way I know is how you spoke, 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

Answer 3

Try this: https://github.com/klebermoura/mssql_array

are procedures that manipulate "array" through "commands" such as "sp_create_array", "sp_set_array", "sp_get_array", "sp_list_array" and several others, solution created by me for a migration from a system in power Builder to T-SQL.

-- TO CREATE AN ARRAY

exec dbo.sp_create_array 'll_dia'

-- SET A(NS) VALUE(ES) IN ARRAY

exec dbo.sp_set_array 'll_dia', 1, 8
exec dbo.sp_set_array 'll_dia', 2, 59898
exec dbo.sp_set_array 'll_dia', 20, 'KLEBER MOURA'

-- GET AN ARRAY VALUE

declare @resultado varchar(8000) 
exec dbo.sp_get_array 'll_dia', 20, @resultado output
select @resultado

-- TO DISPLAY THE ARRAY INDEX BY SEARCHING FOR VALUE

declare @resultado int 
exec dbo.sp_search_array 'll_dia', 'kleber moura', @resultado output
select @resultado

-- LIST ALL ARRAY S CREATED BY ME

exec dbo.sp_list_array 

-- LIST ALL INDECES AND VALUES OF A GIVEN ARRAY

exec dbo.sp_list_array 'll_dia' 

-- DISPOSE - DELETE ALL INDICES FROM THE ARRAY

exec dbo.sp_dispose_array 'll_dia'

-- TO DELETE AN ARRAY

exec dbo.sp_drop_array 'll_dia'

-- TO DISPLAY THE LARGEST ARRAY INDEX (WITH VALUE)

exec dbo.sp_upperBound_array 'll_dia'

-- TO DISPLAY THE SMALLEST ARRAY INDEX (WITH VALUE)

exec dbo.sp_lowerBound_array 'll_dia'   

Answer 4

The best I found for today’s need:

DECLARE @myTableVariable TABLE (id INT, name varchar(20))
insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan')
select * from @myTableVariable

sources:

• https://www.sqlshack.com/implement-array-like-functionality-sql-server/
• https://www.sqlshack.com/the-string-split-function-in-sql-server/