SQL Table Variable

Sample table variable declaration

DECLARE @v_Product TABLE 
(
  ProductId int 
)

We cannot use a table variable as an input or an output parameter. A table variable is scoped to the stored procedure, batch, or user-defined function. The variable will no longer exist after the procedure exits. Although we cannot use a table variable as an input or output parameter, we can return a table variable from a user-defined function. However, because you can’t pass a table variable to another stored procedure as input – there still are scenarios where you’ll be required to use a temporary table when using calling stored procedures from inside other stored procedures and sharing table results. The restricted scope of a table variable gives SQL Server some liberty to perform optimizations. If we are using a table variable in a join, we will need to alias the table in order to execute the query.

SELECT ProductName
FROM Products P
JOIN @v_Product VP
ON VP.ProductId = P.ProductId