Oii Anderson, tudo bem?

OUTPUT Indica que a variável é de saída, ou seja, essa variável vai retornar dados de uma SP. Caso a palavra-chave OUTPUT seja especificada para uma variável na criação da procedure, a procedure poderá retornar o valor atual da variável ao chamar a SP. Já uma variável comum, ela recebe valores normais.

Por exemplo, o seguinte procedimento armazenado encontra produtos por ano do modelo e retorna o número de produtos por meio do @product_count, que é um parâmetro de saída:

CREATE PROCEDURE uspFindProductByModel ( @model_year SMALLINT, @product_count INT OUTPUT ) AS 
BEGIN 
SELECT product_name, list_price 
FROM production.products
WHERE model_year = @model_year; 
SELECT @product_count = @@ROWCOUNT; 
END; 

Neste procedimento armazenado:

Primeiro, criamos um parâmetro de saída denominado @product_count para armazenar o número de produtos encontrados:

@product_count INT OUTPUT 

Em segundo lugar, após o select, atribuímos o número de linhas retornadas pela consulta ( @@ROWCOUNT) ao @product_count.

ROWCOUNT é uma variável do sistema que retorna o número de linhas lidas.

Para executar a SP, pode fazer da seguinte forma:

DECLARE @count INT; 
EXEC uspFindProductByModel @model_year = 2018, 
@product_count = @count OUTPUT; 
SELECT @count AS 'Numero de produtos encontrados';

Primeiro, declare a @count para conter o valor do parâmetro de saída do procedimento armazenado:

DECLARE @count INT;

Em seguida, execute o uspFindProductByModel passando os parâmetros:

EXEC uspFindProductByModel 
@model_year = 2018, 
@product_count = @count OUTPUT;

Nessa instrução, o @model_year (variável comum) é 2018 e a @count atribuem o valor do parâmetro de saída @product_count (variável output).

Espero que isso te ajude a entender melhor. Qualquer coisa é só falar, beleza? :)