Can I work with array in SQL SERVER?

Question

Hello fellow collaborators...

I have a server that has more than 100 banks, and most are of the same structure.

I would like to create a function, process or whatever. That could expedite my maintenance.

I thought about the possibility of having an array named after these banks, and reading this array and firing the necessary commands.

I haven’t gotten results yet, see the following code to see if you understand me:

DECLARE @arrayMinhaBase = [
    0 -> "nomeDoBancoUm",
    1 -> "nomeDoBancoDois",
    2 -> "nomeDoBancoTres",
    3 -> "nomeDoBancoQuatro",
    4 -> "nomeDoBancoCinco",
    5 -> "nomeDoBancoSeis"
    -- etc -> "nomeDoBanco"
];

DECLARE @totalBase int = 1; 
DECLARE @chave int; 

SET @chave = 0;
SET @totalBase = count(@arrayMinhaBase);

WHILE @chave <= @totalBase
  BEGIN

    SELECT * FROM @arrayMinhaBase[$chave].nomeDaTabela;

  END

Well, whatever I get the closest to expediting this process, I’m grateful. The above example I used a select, but most of the time it is to run an alter table or update.

Answer 1

• Create a table type variable to store the name of the banks;
• Enter the names of the banks you want to select values;
• Scroll through the table with a cursor;
• Use the function EXEC to execute a VARCHAR as command.

---

DECLARE @bancos TABLE(nome VARCHAR(100));
DECLARE @banco VARCHAR(100);

INSERT INTO @bancos VALUES('Loteria'),
                          ('Vehicle');

DECLARE cursor_banco CURSOR FOR   
  SELECT b.nome
    FROM @bancos b;
OPEN cursor_banco;

FETCH NEXT FROM cursor_banco
INTO @banco;

WHILE @@FETCH_STATUS = 0  
BEGIN
  EXEC('SELECT * FROM ' + @banco + '.dbo.tabela');

  FETCH NEXT FROM cursor_banco   
  INTO @banco;
END   
CLOSE cursor_banco;  
DEALLOCATE cursor_banco;