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