Variable error

[fredong]

Hi,
I have a variable error that stated that I have to declare @table which I already declare when I run my query below. Please advise. Thanks

DECLARE @YR varchar(4)
DECLARE @QTR varchar(3)
DECLARE @Table varchar(30)


SET @YR = Year(getdate())
If Month(getdate()) IN (1,2,3)
SET @QTR = '_Q1'
If Month(getdate()) IN (4,5,6)
SET @QTR = '_Q2'
If Month(getdate()) IN (7,8,9)
SET @QTR = '_Q3'
If Month(getdate()) IN (10,11,12)
SET @QTR = '_Q4'

SET @Table = 'dbo.AUDIT2' + @YR

select * from @Table (nolock)

 

[AlexCuse]

When you do 'select * from @table', it expects a table variable. Try something like this:

set @table = 'dbo.AUDIT2' + @YR  

declare @sql varchar(100)
set @sql = 'select * from ' + @table

exec(@sql)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fredong]

is there any other way other than Dynamic SQL? By the thanks for your help.

 

[AlexCuse]

I think that you might be out of luck. You could simply hard-code the full table name with the year included, and adjust your proc each year, but who wants to do that?

Sorry,

Alex

Ignorance of certain subjects is a great part of wisdom