Declare table variable issue in SQL Server

Question

I want to declare a table variable and I followed the steps in this page but I get error on the line SELECT @StdRevenue;. It seems that I need to declare the variable - which I did.

The table Purchasing.ProductVendor:

My code

DECLARE @StdRevenue TABLE
(
  ProductID int, 
  Revenue money
);

--insert values to columns
INSERT INTO @StdRevenue (ProductID, Revenue)
    SELECT ProductID, StandardPrice * OnOrderQty 
    FROM Purchasing.ProductVendor
    GROUP BY ProductID;

SELECT @StdRevenue;

Do you mean `SELECT * from @StdRevenue`? Its a table after all... — Dale K, Dec 17 '18 at 03:57

score 2 · Answer 1 · edited Dec 17 '18 at 06:13

I hope below queries help you....

Create table #temp

(
id int,
price varchar(5),
qty int
)

insert into #temp values(1,'50',1)

insert into #temp values(2,'500',4)

insert into #temp values(3,'100',6)



--Select All queries together

DECLARE @StdRevenue TABLE
(
  ProductID int, 
  Revenue money
);

--insert values to columns

INSERT INTO @StdRevenue (ProductID, Revenue)
    SELECT id, price * qty 
    FROM #temp


SELECT * from @StdRevenue;

----Till here

score 2 · Accepted Answer · answered Dec 17 '18 at 06:17

Use

SELECT * FROM @StdRevenue;

instead of

SELECT @StdRevenue;

DECLARE @StdRevenue TABLE
(
  ProductID int, 
  Revenue money
);

--insert values to columns
INSERT INTO @StdRevenue (ProductID, Revenue)
    SELECT ProductID, StandardPrice * OnOrderQty 
    FROM Purchasing.ProductVendor
    GROUP BY ProductID;

SELECT * FROM @StdRevenue;

score 1 · Answer 3 · answered Dec 17 '18 at 06:18

Try the following code

DECLARE @StdRevenue TABLE
(
  ProductID int, 
  Revenue money
);

--insert values to columns
INSERT INTO @StdRevenue (ProductID, Revenue)
    SELECT 1, 2

SELECT * from @StdRevenue;

Here you have missed to write * from before table name like @StdRevenue

Declare table variable issue in SQL Server

3 Answers3