0

I have a table called CourceInstance which includes a Period and a Year attribute. I am trying to put the values of Period attribute from CourseInstance into my table variable. It can run without a problem however every time I run the EXEC @tablePeriod part or the whole code it gives me the error:

Must declare the scalar variable "@tablePeriod".

What can the problem be of? @tablePeriod is already declared.

DECLARE @tablePeriod TABLE ( Period INT ) INSERT INTO @tablePeriod (Period)
SELECT Period FROM CourseInstance WHERE Year = 2015

EXEC @tablePeriod
Improve this question
5
  • Sure, it is declared, but it is not scalar, because it is a table-typed variable. Commented May 24, 2016 at 10:43
  • 2
    What do you expect EXEC @tablePeriod to do? I'm genuinely unable to guess what your intention was here. Commented May 24, 2016 at 10:46
  • @dasblinkenlight I dont understand the meaning of scalar in this situation. Commented May 24, 2016 at 10:50
  • @Damien_The_Unbelieverto show a table including the values of Period from CourseInstance. Something like a view of the column CourseInstance.Period Commented May 24, 2016 at 10:51
  • @Tomb_Raider_Legend "Scalar" means "with a single value", such as a single number or a single string. In this context that's the opposite of "table", which may have more than one value organized as rows and columns, or no values at all. Commented May 24, 2016 at 10:59

2 Answers 2

Reset to default
1

I'd expect 

EXEC @tablePeriod

should be something like 

select * from @tablePeriod

Then it won't return error but you have to run whole code in one block.

Running the code that uses the variable separately from the declaration statement is giving you error.

Edit: Now I guess I know what you want to achieve. Actually the DECLARE and INSERT statement are enough for that. No need for something else.

Running

select * from @tablePeriod

in same block will show you that the values are inserted into table variable.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I am doing it as well but yet it gives me the error. I have declared this variabel many times before but stopped using it due to code failures. Now I am declaring it again in another project. The problem migh be because of that maybe?
The problem is at least in how you use it, the "EXEC".
thanks for the help. IT works. But in which situation do you use EXEC on the variabel? I only seen people do it in some other examples
@Tomb_Raider_Legend In case of Dynamic SQL, for instance. I.e. DECLARE @sql nvarchar(max); SET @sql = 'SELECT * FROM @myTable'; EXEC @SQL. You'd typically use something like this in situations where you perform operations on different tables. Think of a loop in which you do some table or column modifications.
1

You can try with tempory table. I think tempory table is better than table variable. 

SELECT Period INTO #tablePeriod FROM CourseInstance WHERE Year = 2015;
SELECT * FROM #tablePeriod;
Improve this answer

2 Comments

Why would you argue it's better? What's the motivation?
@SchmitzIT Both have advantages and disadvantages.I only mean for above question because code is much shorter and no need to create extra table varible. I don't mean for all condition. I add link for reference of performance mssqltips.com/sqlservertip/2825/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.