table_variable.sql

-- Variáveis de Tabela
-- são variáveis que permitem armazenar linhas, como tabelas temporárias
-- podem ser declaradas com o comando DECLARE:
/*
DECLARE @table_variable_name TABLE (
    column_list
);
*/
-- Essas variáveis perdem o escopo no fim do script
-- se definida em uma função ou procedure ela deixa de existir após
-- o fim da função ou procedure
-- por exemplo, declarar @product_table que possui três colunas: 
-- product_name, brand_id e list_price:
DECLARE @product_table TABLE (
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);
-- ao declarar, a variável está vazia e pode ser preenchida com o comando INSERT
INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;
-- podemos consultá-la com o comando SELECT
SELECT
    *
FROM
    @product_table;

-- Restrições
-- 1. a estrutura deve ser definida na declaração e não pode ser redefinida
-- 2. não há "estatísticas para o otimizador de consultas", então a variável
--    deve conter poucas linhas (sob pena de redução de desempenho)
-- 3. não pode ser usada como parâmetro de entrada ou saida, como outros tipos de dados
--    mas pode ser retornada por uma função
-- 4. ao usar uma variável-tabela em um JOIN, é preciso usar um ALIAS para executar
--    a consulta, por exemplo:
SELECT
    brand_name,
    product_name,
    list_price
FROM
    brands b
INNER JOIN @product_table pt 
    ON p.brand_id = pt.brand_id;

-- exemplo de função que retorna uma table-variable
CREATE OR ALTER FUNCTION udfSplit(
    @string VARCHAR(MAX), 
    @delimiter VARCHAR(50) = ' ')
RETURNS @parts TABLE
(    
idx INT IDENTITY PRIMARY KEY,
val VARCHAR(MAX)   
)
AS
BEGIN

DECLARE @index INT = -1;

WHILE (LEN(@string) > 0) 
BEGIN 
    SET @index = CHARINDEX(@delimiter , @string)  ;
    
    IF (@index = 0) AND (LEN(@string) > 0)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (@string);
        BREAK  
    END 

    IF (@index > 1)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (LEFT(@string, @index - 1));
        
        SET @string = RIGHT(@string, (LEN(@string) - @index));  
    END 
    ELSE
    SET @string = RIGHT(@string, (LEN(@string) - @index)); 
    END
RETURN
END
GO

-- e aqui o exemplo de chamada a essa função
SELECT 
    * 
FROM 
    udfSplit('foo,bar,baz',',');