1

I'm writing the following code into SQL Server Management Studio

DECLARE @tab AS table(ProductID integer, StockCount integer)
INSERT INTO @tab
SELECT ProductID, InStock + OnOrder
FROM Inventory.Product;
GO
SELECT * FROM @tab;

When I execute the code, an error occurs. Which of the two following actions could I take to prevent the error from happening?

1

Modify the INSERT statement to:
INSERT INTO @tab
SELECT ProductID, InStock
FROM Inventory.Product;

2

--Remove the GO command

3

--Use a temporary table named #tab instead of the @tab variable

4

--Add a second GO command after the final SELECT statement

Personally, I think 1 and 2 are correct, but with slight disability issues I'm not confident enough in my answer being 100% correct, if anyone could give any pointers that would certainly help or explain why I may be wrong.

EDIT: THE ERROR I'M GIVEN WHEN I RUN A QUERY IS:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'Inventory.Product'.
Msg 1087, Level 15, State 2, Line 6
Must declare the table variable "@tab".
jrodden
  • 39
  • 1
  • 6

3 Answers3

1

The reason you are getting the error is because a Variable's scope is the transaction in which it is declared.

When you declare a variable as soon as you put the GO key word which is a batch separator the variable is not visible after that GO keyword.

Anyway these are not the factors which should decide whether to use a Table Variable or a Temp table.

Have a look at this question What's the difference between a temp table and table variable in SQL Server to learn about the differences between the Temp tables and table variables and then decide what is best for you.

Community
  • 1
  • 1
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • I'm still struggling after reading that post, from my understanding, that suggests I need to add another GO command (4) because there is a batch separator? Furthermore, that I also need should use a #tab because it's stored in tempdb? Or have I misunderstood? – jrodden Jun 27 '16 at 22:16
  • yes the temp tables always and table variables sometimes exist in the tempdb, also why do you think you need to add `GO` in your script ? – M.Ali Jun 27 '16 at 22:27
  • Seems the answer was incorrect, I selected 3 and 4 and the answer was incorrect unfortunately. – jrodden Jun 27 '16 at 22:38
0

I would say 2 & 3 are correct. The Go will not allow the variable to carry over to the second query, but you could get around this by using a temporary table instead of a variable.

TyM
  • 53
  • 4
0

2 & 3 are correct indeed.

  1. Remove the go makes it as a single batch, so @tab var would be accessible for second SELECT stmnt
  2. If you need though to split it into two batches, then as advised before - make it a table.
user248297
  • 1