Error message: Must declare the table variable "@g

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Error message: Must declare the table variable "@g

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-04 : 12:41:26

I execute the below command that aims to generate dates outputs successfully:

DECLARE @#gencalendar TABLE (cal_date DATETIME PRIMARY KEY)
DECLARE @p_date SMALLDATETIME
DECLARE @date DATE

SET @p_date = '2010-10-28'

WHILE @date <= '2015-10-28'

BEGIN

INSERT INTO @#gencalendar(cal_date)
VALUES(@p_date)

SET @date = dateadd(d, 1, @p_date)
END

_____

However when I try to select * from @#gencalender, it gives me the following error:

Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@#gencalendar".

Can someone tell me what do I do wrong? I appreciate your help a lot!

thanks

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-04 : 12:48:30

I forgot to say that I am using Microsoft SQl server Management studio 2005. thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-04 : 13:51:06

you have to perform the select in the same batch. table variables are scoped at the batch level.

also, note that your insert script doesn't do anything. this is what you want:

DECLARE @#gencalendar TABLE (cal_date DATETIME PRIMARY KEY)
DECLARE @p_date SMALLDATETIME

SET @p_date = '2010-10-28'

WHILE @p_date <= '2010-11-28'
BEGIN

INSERT INTO @#gencalendar(cal_date)
VALUES(@p_date)

SET @p_date = dateadd(d, 1, @p_date)
END

select * from @#gencalendar

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-04 : 13:56:19

IT WORKED! Thanks so much!! Now i need to filter the results by weekday - I was thinking to use the where function but not do not think it will work since the weekdays (mon-tue, etc) are not displayed in the results. Any ideas how else I can filter to have only the the dates that are weekdays displayed? thanks!

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-04 : 14:09:35

One more question - I am trying to filter the results using where [weekday] = 'Sunday' but it gives me an error of invalid column name although I defined it in the script using the AS 'Weeday" command. Why this is happening and how can I fix it?

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-04 : 14:11:10

WHERE DatePart(dw, cal_date) = ?

WHERE DateName(dw, cal_date) = 'Wednesday'

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-04 : 14:20:26

Yes worked - thanks! I might have a few other questions in the process! thanks for your help Russell!

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-04 : 14:23:00

Ah one more thing - how can I select the final results i have into one temp table which I can query further? thanks